M0 · CatalogProduct schema

First milestone of the V3 datamodel migration: an append-only, per-SKU versioned CatalogProduct table that the rest of the M1→M9 stack builds on.

PR dealops#5952 Author @pk675 Ticket DEA-6301 Parent DEA-6258 Branch pk/catalog-product-m0 Files 6 +/− +344 / −0 Status Proposed

M0 · schema M1 M2 M3 M4 M5 M6 M7 M8 M9

What it adds

A new CatalogProduct Prisma model, its SQL migration, app-layer TypeScript types, and schema-only tests. One row = one published version of one SKU.

Shape

Append-only, per-product versioned. Live read = max version per (orgId, skuId). Rollback read = latest row with publishedAt ≤ K.

What it doesn't do

No tRPC routes, no writes, no UI, no data backfill. M0 is purely schema + types + tests. Prod DBs are untouched until prisma migrate deploy runs.

CatalogProduct (new)

Organization (FK)

User (FK)

OrgGlobalVersion (logical link)

1. Why this exists

DEA-6301 is the first ticket in the V3 datamodel / Product Catalog Migration (parent DEA-6258). The whole stack M1 → M9 depends on having a versioned product table in place, so this PR ships the schema, the migration, the app types, and the tests that don't need a live DB — and nothing else.

Live catalog read

For each (orgId, skuId), take the row with the highest version. The descending index on (orgId, skuId, version DESC) makes this an index-only top-1 lookup.

As-of-K rollback read

Given a target global version K, take the row with the highest publishedAt ≤ K. The second descending index on (orgId, skuId, publishedAt DESC) serves this.

Lifecycle bits

isActive=false = deactivated (still in catalog).
isDeleted=true = tombstoned (removed from catalog). Two distinct states, both expressible without deleting rows.

2. The model

The full schema lives in packages/prisma/schema/models/v3/catalog-product.prisma. Here's the column-by-column shape:

Column	Type	Notes
id	String	PK uuid default
orgId	String	FK → `Organization.id`
skuId	String	Stable identity of the product across versions
version	Int	Per-product, 1-based; monotonic per `(orgId, skuId)`
publishedAt	Int	= `OrgGlobalVersion.version` at publish time (not a wall clock)
systemAttributes	Json	System-managed attributes
customAttributes	Json	Default `{}`
price	Json	Default `{}`
crmProductId	String?	Dedicated CRM write-back id, promoted out of `systemAttributes`
isActive	Boolean	Default `true` — deactivated when false
isDeleted	Boolean	Default `false` — tombstone when true
publishedByUserId	String	FK → `User.id`
createdAt	DateTime	Default `now()`

Constraints and indexes

UNIQUE

(orgId, skuId, version)
Hard-enforces "one row per published version per SKU per org" — duplicate publishes blow up at the DB.

INDEX live-read

(orgId, skuId, version DESC)
Serves the "max-version per SKU" query that drives the live catalog view.

INDEX rollback-read

(orgId, skuId, publishedAt DESC)
Serves the "as-of global version K" query used during rollback.

3. The migration

One file: packages/prisma/migrations/20260615181912_catalog_product/migration.sql. Generated by prisma migrate dev against a throwaway localhost DB and verified to apply cleanly. The PR notes explicitly: no prod / Neon DB was touched.

View migration SQL

CREATE TABLE "CatalogProduct" (
    "id" TEXT NOT NULL,
    "orgId" TEXT NOT NULL,
    "skuId" TEXT NOT NULL,
    "version" INTEGER NOT NULL,
    "publishedAt" INTEGER NOT NULL,
    "systemAttributes" JSONB NOT NULL,
    "customAttributes" JSONB NOT NULL DEFAULT '{}',
    "price" JSONB NOT NULL DEFAULT '{}',
    "crmProductId" TEXT,
    "isActive" BOOLEAN NOT NULL DEFAULT true,
    "isDeleted" BOOLEAN NOT NULL DEFAULT false,
    "publishedByUserId" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "CatalogProduct_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "CatalogProduct_orgId_skuId_version_idx"
    ON "CatalogProduct"("orgId", "skuId", "version" DESC);
CREATE INDEX "CatalogProduct_orgId_skuId_publishedAt_idx"
    ON "CatalogProduct"("orgId", "skuId", "publishedAt" DESC);
CREATE UNIQUE INDEX "CatalogProduct_orgId_skuId_version_key"
    ON "CatalogProduct"("orgId", "skuId", "version");

ALTER TABLE "CatalogProduct" ADD CONSTRAINT "CatalogProduct_orgId_fkey"
    FOREIGN KEY ("orgId") REFERENCES "Organization"("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "CatalogProduct" ADD CONSTRAINT "CatalogProduct_publishedByUserId_fkey"
    FOREIGN KEY ("publishedByUserId") REFERENCES "User"("id")
    ON DELETE RESTRICT ON UPDATE CASCADE;

M0/M1 folded together. The PR mentions that crmProductId was originally planned for M1 but has been pulled into M0 so the table is created complete in a single shot. No follow-on ALTER TABLE needed before M2+.

4. App-layer types

In apps/server/src/dealops3/catalogProduct/types.ts the application gets two types. They deliberately diverge from the generated Prisma row in two ways: the org FK is renamed, and the JSON columns are narrowed.

CatalogProductRow

Full app-layer row shape. Notably:

organizationId instead of orgId — matches the rest of the dealops3 layer.
JSON columns typed as Record<string, unknown> rather than Prisma's broad JsonValue.
crmProductId is nullable.

CatalogProductContent

The publish-time mutable subset — what a caller actually supplies:

systemAttributes
customAttributes
price
crmProductId
isActive

Identity, versioning, and audit columns are assigned by the store.

5. Tests — what they do and don't cover

The suite in apps/server/src/dealops3/__tests__/catalogProduct.test.ts sticks strictly to checks that don't require a live DB or an applied migration. Three buckets:

1 · Client presence

The PrismaClient['catalogProduct'] delegate type exists (compile-time) and prisma.catalogProduct.findMany / .create exist on the generated client at runtime.

2 · Type alignment

A series of conditional-type assignability checks (Assignable<A,B>) ensure CatalogProductRow field types match the generated row, and that CatalogProductContent is exactly a Pick of the right keys.

3 · Sandbox scoping

Feeds a synthetic SchemaInfo to buildPlan and asserts the planner auto-derives { kind: 'direct', column: 'orgId' } — no registry.ts entry needed.

Why the sandbox-extract check matters

The dealops sandbox-extract pipeline must know how each table is scoped to an org. For tables with a single-column FK to Organization.id, the rule is auto-derived. The test ensures that adding CatalogProduct doesn't require a manual registry entry — and crucially, that the second FK to User (publishedByUserId) doesn't confuse the auto-deriver.

const plan = buildPlan(makeSchema());
const cp = plan.tables.find((t) => t.table === 'CatalogProduct');
assert.deepStrictEqual(cp.rule, { kind: 'direct', column: 'orgId' });

What's deferred

One describe.skip block remains. The DB-backed check — that the unique constraint actually rejects a duplicate insert — is stubbed out as pseudocode. It becomes runnable once a reviewer runs prisma migrate dev --name catalog_product against localhost. The PR is explicit about this and keeps the suite green pre-migration.

6. What it doesn't change

No tRPC routes, no resolvers, no service layer — pure schema + types.
No reads or writes against CatalogProduct anywhere in the app code yet.
No registry.ts entry in sandbox-extract (the test asserts it isn't needed).
No data backfill from legacy Dealops 1 product / pricingcurve tables.
No prod DB writes. prisma migrate deploy will create the table on the next deploy; no manual ops required.
Organization and User models only gain a back-relation field (catalogProducts, publishedCatalogProducts) — no column changes.

7. Risks & open questions

Low risk. Append-only new table, no existing data migrated, no read or write paths wired up. The migration was verified locally; rollback is a single DROP TABLE.

Worth flagging at review time:

Migration filename timestamp. The directory name is 20260615181912_catalog_product — a 2026 timestamp. Prisma orders migrations lexicographically, so a date this far in the future will sort after every migration generated until mid-2026. Worth confirming this is intentional or regenerating.
publishedAt as Int. The column is an integer foreign reference to OrgGlobalVersion.version, but there is no DB-level FK constraint to that table. The relationship is logical only — fine as long as the publish path always reads from OrgGlobalVersion first.
JSON columns are unconstrained. systemAttributes, customAttributes, and price are raw JSONB with no schema. Validation lives in app code; later milestones will presumably formalize.
ON DELETE RESTRICT on both FKs. Deleting an Organization or User that has any catalog rows will fail. Intentional for append-only history, but worth being aware of.

8. What comes next

This is M0 of a 10-milestone chain. The PR description points at docs/catalog-product-tickets.md for the full breakdown. M1 (originally going to add crmProductId) has been partly absorbed here; M2 onward presumably layers on the publish / read / rollback service code, tRPC surface, and eventually the UI + backfill from Dealops 1.