-- PAX8 integration — Postgres schema. -- -- Lays down the full PAX8 schema Phases 11-14 will populate and consume. -- Schema-only migration (Phase 10) — no sync logic, no matching logic yet. -- -- • Client companies -> pax8_companies -- • Active seat/license subscriptions -> pax8_subscriptions -- • Product/SKU catalog -> pax8_products -- • Order headers (Invoice-shaped, see note below) -> pax8_orders -- • Order line items (InvoiceItem-shaped) -> pax8_order_items -- • PAX8 company <-> Autotask company conflicts -> pax8_company_match_review -- -- NOTE on pax8_orders / pax8_order_items: table names stay "orders" per the -- D-01 header/line-item design decision, but the column shapes below carry -- PAX8 Invoice/InvoiceItem semantics (total, status, unit price) rather than -- the bare Order/LineItem object fields — Phase 12's sync service sources -- these from PAX8's `/invoices` resource, not `/orders`. Confirm the exact -- field mapping against PAX8's live API response when Phase 12 is planned. -- --------------------------------------------------------------------------- -- 1. pax8_companies — PAX8 client companies -- --------------------------------------------------------------------------- -- external_id is left nullable and separate from name: PAX8's own company id -- is the primary key, but there is no confirmed stable secondary identifier -- (domain, external ref) yet — RESEARCH-pax8-company-identifiers is still -- open. Do not assume company name is the only field comparable to Autotask. CREATE TABLE IF NOT EXISTS pax8_companies ( id UUID PRIMARY KEY, name TEXT NOT NULL, external_id TEXT, website TEXT, status TEXT, city TEXT, state_or_province TEXT, postal_code TEXT, country TEXT, raw_payload JSONB, synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), is_deleted BOOLEAN NOT NULL DEFAULT false, deleted_at TIMESTAMPTZ ); CREATE INDEX IF NOT EXISTS idx_pax8_companies_is_deleted ON pax8_companies(is_deleted); -- --------------------------------------------------------------------------- -- 2. pax8_products — PAX8 product/SKU catalog -- --------------------------------------------------------------------------- -- No "referenced only" constraint here on purpose: whether Phase 11 syncs -- the full catalog or lazily populates rows as referenced by subscriptions -- is left open (CONTEXT.md Claude's Discretion). The deprecated alternate- -- SKU field upstream is not modeled. CREATE TABLE IF NOT EXISTS pax8_products ( id UUID PRIMARY KEY, sku TEXT, vendor_sku TEXT, name TEXT, category TEXT, raw_payload JSONB, synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), is_deleted BOOLEAN NOT NULL DEFAULT false, deleted_at TIMESTAMPTZ ); CREATE INDEX IF NOT EXISTS idx_pax8_products_is_deleted ON pax8_products(is_deleted); -- --------------------------------------------------------------------------- -- 3. pax8_subscriptions — active seat/license subscriptions per company -- --------------------------------------------------------------------------- -- pax8_company_id / product_id are intentionally soft refs (plain indexed -- columns, not hard FKs) — sync insert order across companies/subscriptions/ -- products is not guaranteed within a single sync pass. CREATE TABLE IF NOT EXISTS pax8_subscriptions ( id UUID PRIMARY KEY, pax8_company_id UUID, -- soft ref -> pax8_companies(id) product_id UUID, -- soft ref -> pax8_products(id) quantity INTEGER, billing_term TEXT, status TEXT, start_date TIMESTAMPTZ, raw_payload JSONB, synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), is_deleted BOOLEAN NOT NULL DEFAULT false, deleted_at TIMESTAMPTZ ); CREATE INDEX IF NOT EXISTS idx_pax8_subscriptions_is_deleted ON pax8_subscriptions(is_deleted); CREATE INDEX IF NOT EXISTS idx_pax8_subscriptions_company ON pax8_subscriptions(pax8_company_id); -- --------------------------------------------------------------------------- -- 4. pax8_orders — order/invoice header (D-01 header table) -- --------------------------------------------------------------------------- -- Invoice-shaped: total/status/currency mirror PAX8's Invoice object per -- RESEARCH Pitfall 1. No lookback-window column (D-02) — sync pulls full -- order history on first sync in a later phase. CREATE TABLE IF NOT EXISTS pax8_orders ( id UUID PRIMARY KEY, pax8_company_id UUID, -- soft ref -> pax8_companies(id) order_date TIMESTAMPTZ, total NUMERIC(12,2), status TEXT, currency CHAR(3) NOT NULL DEFAULT 'USD', raw_payload JSONB, synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), is_deleted BOOLEAN NOT NULL DEFAULT false, deleted_at TIMESTAMPTZ ); CREATE INDEX IF NOT EXISTS idx_pax8_orders_is_deleted ON pax8_orders(is_deleted); CREATE INDEX IF NOT EXISTS idx_pax8_orders_company ON pax8_orders(pax8_company_id); -- --------------------------------------------------------------------------- -- 5. pax8_order_items — order/invoice line items (D-01 line table) -- --------------------------------------------------------------------------- -- InvoiceItem-shaped: unit_price/line_total mirror PAX8's InvoiceItem object -- per RESEARCH Pitfall 1. Hard FK to pax8_orders — header/line relationship, -- unlike the company/product soft refs above, because order_items are always -- inserted together with (after) their parent order in a single sync pass. CREATE TABLE IF NOT EXISTS pax8_order_items ( id UUID PRIMARY KEY, order_id UUID NOT NULL REFERENCES pax8_orders(id) ON DELETE CASCADE, product_id UUID, -- soft ref -> pax8_products(id) quantity INTEGER, unit_price NUMERIC(12,2), line_total NUMERIC(12,2), currency CHAR(3) NOT NULL DEFAULT 'USD', raw_payload JSONB, synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), is_deleted BOOLEAN NOT NULL DEFAULT false, deleted_at TIMESTAMPTZ ); CREATE INDEX IF NOT EXISTS idx_pax8_order_items_is_deleted ON pax8_order_items(is_deleted); CREATE INDEX IF NOT EXISTS idx_pax8_order_items_order ON pax8_order_items(order_id); -- --------------------------------------------------------------------------- -- 6. pax8_company_match_review — PAX8 <-> Autotask company conflicts queue -- --------------------------------------------------------------------------- -- Copied field-for-field from device_link_review (migration 080). Populated -- by Phase 12's matching logic — this migration only creates the shape. -- Admin picks the right Autotask company; the matcher does not auto-merge. CREATE TABLE IF NOT EXISTS pax8_company_match_review ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), pax8_company_id UUID NOT NULL REFERENCES pax8_companies(id) ON DELETE CASCADE, candidate_company_ids BIGINT[] NOT NULL, match_confidences TEXT[] NOT NULL, detected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), resolved_at TIMESTAMPTZ, resolved_by_user_id TEXT REFERENCES "user"(id) ON DELETE SET NULL, resolved_to_company_id BIGINT REFERENCES companies(id) ON DELETE SET NULL, resolution_note TEXT ); CREATE INDEX IF NOT EXISTS ix_pax8_company_match_review_unresolved ON pax8_company_match_review(detected_at DESC) WHERE resolved_at IS NULL; CREATE INDEX IF NOT EXISTS ix_pax8_company_match_review_pax8_company ON pax8_company_match_review(pax8_company_id); -- Don't keep stale rows blocking new reviews: partial unique index enforces -- "one open review per PAX8 company" rather than a blanket unique constraint. CREATE UNIQUE INDEX IF NOT EXISTS uq_pax8_company_match_review_open ON pax8_company_match_review(pax8_company_id) WHERE resolved_at IS NULL; COMMENT ON TABLE pax8_company_match_review IS 'PAX8 -> Autotask company-match conflicts queue: one row per PAX8 company that matches 2+ Autotask companies (or is otherwise ambiguous). Populated by Phase 12''s matching logic. Admin picks the right Autotask company; the matcher does not auto-merge.';