2026-10-18-schema-0020

Decisioncanonicalverified 2026-10-18

DECISION.2026-10-18.SCHEMA-0020

The schema for the editor redesign (0020)

Approved by the owner on 2026-10-18. The one migration is written fresh (MVP: no data to keep).

Decision

Eleven changes to packages/db/prisma/schema.prisma. The database is dropped, the one migration is written again, and the seed moves to Chitragupt. Tables go from 42 to 44: 3 removed, 5 added.

1. Product access replaces teams

Remove Team, TeamMember, ProductTeam, and Invite.teamId.
Add Product.access (EVERYONE by default, or CHOSEN) and a join table ProductMember for the chosen people.
Row-level security: a member sees a product when its access is EVERYONE, when they are in ProductMember, or when they are the Owner.

enum ProductAccess {
  EVERYONE
  CHOSEN
}

// Who can open a CHOSEN product, besides the Owner. The person must be a member
// of the same company; removing the membership removes the row.
model ProductMember {
  clientId  String   @db.Uuid
  productId String   @db.Uuid
  userId    String   @db.Uuid
  createdAt DateTime @default(now()) @db.Timestamptz(3)

  product    Product    @relation(fields: [productId, clientId], references: [id, clientId], onDelete: Cascade)
  membership Membership @relation(fields: [clientId, userId], references: [clientId, userId], onDelete: Cascade)

  @@id([productId, userId])
  @@index([clientId, userId])
}

2. Roles at capture

Add ProductRole. Every product has one Open role, made with the product, which cannot be deleted.
Add RoleCredential for a role's test sign-in. The password is encrypted (AES-256-GCM). The key lives in the worker's environment, never in the database.
Who can read the password: the web app may write it and delete it, but the database refuses to let the web app read the secret column. Only the worker's database role can read it.
Plan limit: the free month allows Open and one more role. The app checks this, because the limit depends on the plan.

// A kind of user of the captured product: Open (signed out), User, CA, Admin.
// SQL: exactly one isOpen role per product; it cannot be deleted.
model ProductRole {
  id         String   @id @default(uuid(7)) @db.Uuid
  clientId   String   @db.Uuid
  productId  String   @db.Uuid
  name       String
  isOpen     Boolean  @default(false)
  /// The page the worker signs in on. Empty means the product's own sign-in link.
  signInPath String?
  /// The account asks for a code (2FA) or uses single sign-on, so it is walked by hand.
  needsWalk  Boolean  @default(false)
  position   Int
  createdAt  DateTime @default(now()) @db.Timestamptz(3)
  updatedAt  DateTime @default(now()) @updatedAt @db.Timestamptz(3)

  product     Product         @relation(fields: [productId, clientId], references: [id, clientId], onDelete: Cascade)
  credential  RoleCredential?
  captureRuns CaptureRun[]
  pages       PageRole[]

  @@unique([id, productId, clientId])
  @@unique([productId, name])
}

// A role's test sign-in. The web app may INSERT, UPDATE and DELETE it; only the
// worker's database role may SELECT secretCipher (column privilege in SQL).
model RoleCredential {
  roleId       String   @id @db.Uuid
  clientId     String   @db.Uuid
  productId    String   @db.Uuid
  email        String
  /// AES-256-GCM: nonce, ciphertext and tag together.
  secretCipher Bytes
  /// Which key encrypted it, so keys can be rotated.
  keyId        String
  updatedAt    DateTime @default(now()) @updatedAt @db.Timestamptz(3)

  role ProductRole @relation(fields: [roleId, productId, clientId], references: [id, productId, clientId], onDelete: Cascade)
}

3. A capture runs as one role

Add CaptureRun.roleId. It is required: one run per role.

4. Which roles reach a page

Add PageRole. The capture writes it: a page reached by several roles is one page with several rows. For a designed page, the roles are a change on the page (property roles), like any other edit.

model PageRole {
  clientId  String @db.Uuid
  productId String @db.Uuid
  pageId    String @db.Uuid
  roleId    String @db.Uuid

  page Page        @relation(fields: [pageId, productId, clientId], references: [id, productId, clientId], onDelete: Cascade)
  role ProductRole @relation(fields: [roleId, productId, clientId], references: [id, productId, clientId], onDelete: Cascade)

  @@id([pageId, roleId])
  @@index([roleId])
}

5. A journey link has an action

Add to JourneyEdge:
- action: Tap, Submit a form, After sign-in, Redirect or Back.
- selector: the element the action is on.
- origin: CAPTURED, or CREATED when someone draws it in Edit.
Change the unique rule. Today two pages can have only one link between them. The new rule allows one link per element and action, so two pages can have several arrows.
A drawn link is a CREATE change, like every made thing. Its label and action can be changed (JOURNEY_EDGE changes).

enum LinkAction {
  TAP
  SUBMIT
  AFTER_SIGN_IN
  REDIRECT
  BACK
}
// JourneyEdge gains:
//   action   LinkAction      @default(TAP)
//   selector String          @default("")
//   origin   PrimitiveOrigin @default(CAPTURED)
//   @@unique([fromPageId, toPageId, action, selector])   (replaces [fromPageId, toPageId])

6. Journeys of your own

Add Journey. Only what never changes is on the row. Its name, start page and pages are changes (new target kind JOURNEY), so it goes through the draft, Review and merge like everything else, and everyone sees it once it is merged.

model Journey {
  id        String   @id @default(uuid(7)) @db.Uuid
  clientId  String   @db.Uuid
  productId String   @db.Uuid
  createdAt DateTime @default(now()) @db.Timestamptz(3)

  product Product @relation(fields: [productId, clientId], references: [id, clientId], onDelete: Cascade)

  @@unique([id, productId, clientId])
}
// TargetKind gains JOURNEY   property: name | start | pages (an ordered list of page ids)

7. Logos, not illustrations

Change AssetKind from ICON | ILLUSTRATION to ICON | LOGO. A logo keeps its own colours (monochrome = false). It is listed first in Styles › Icons.

8. The Styles groups

Add FONT and MOTION to TokenKind, which becomes: COLOR, FONT, TYPE, SPACE, RADIUS, SHADOW, MOTION.
Icons stay assets.

9. Light and dark

Add User.appearance, LIGHT by default or DARK.

enum Appearance {
  LIGHT
  DARK
}
// User gains:  appearance Appearance @default(LIGHT)

10. Clear score names

Rename VerifyRun.score to matchScore.
Rename AnalysisRun.consistency to designConsistency.

They are different numbers, so they never share a name.

11. Small fixes

Product.url's comment says it is asked for on Setup, and the name and slug are derived from it.
Ref's comment: "Starter's 2 open branches" becomes "the free month's 2 open branches".

Why

Each change follows from 2026-10-18-editor-redesign and demo-tenants:

Change	Comes from
Roles, credentials, page roles	Roles at capture
Link action, own journeys	Journey
Logos	Icons has Logos, no Illustrations
Font and Motion	One page per Styles group
Appearance	Light and dark
Product access	Teams fold into product sharing
Score names	Verify and Analysis are two different scores

Keeping a journey's name and pages as changes, not columns, means it is reviewed, synced and merged with no new rules. This is the same choice as 2026-10-17-drawn-elements.

Impact

Code:
- The capture worker runs once per role and decrypts the role's password only in memory.
- Everything that reads teams moves to product access.
- The seed moves to Chitragupt and Chitragupt Android.
Security: a new secret kind. The key is ROLE_CREDENTIAL_KEY, kept in the worker's .env and backed up to ~/git/local/secrets/products/trunk/. Its name goes in INVENTORY.md.
Tests:
- Row-level security for ProductMember, ProductRole, PageRole, Journey and RoleCredential.
- The web role cannot read secretCipher.
- One Open role per product.
- The free-month role limit.
packages/ui/src/theme.test.ts compares both palettes once the app has the light theme.

Status

Accepted 2026-10-18.

Sources

The owner's decisions in the session of 2026-10-18.