Skip to content

Work / Trunk / Wiki / Decisions

2026-10-23-schema-redesign

Decisioncanonicalverified 2026-10-23

DECISION.2026-10-23.SCHEMA-REDESIGN

The schema for the redesign

Decision

A new schema of 40 tables (from 50), in packages/db/prisma/schema.prisma. It replaced the old one on approval; the database is dropped and the one migration written again (no data is kept — zero legacy). Better Auth's plugins add their own few tables for two-step sign-in, OAuth (MCP) and the CLI's device login.

Area Tables What they hold
People and sign-in (5) User · Session · Account · Verification · ApiToken Better Auth's four, plus personal tokens for the CLI (hash only). Theme and email choices are columns on User
Companies (6) Client · Membership · Invite · Product · ProductMember · ProductRole The company with its plan and status; members with an access level and a lastSeenAt heartbeat; 7-day invites; products with the way in, sizes, one design or per size, stack, access and capture settings; chosen people; roles with sealed test passwords
Work (2) Job · Capture One queue for everything slow (capture, verify, analysis, images, export, email) with retries and runAfter; a capture record per run
The design (11) Page · PageSnapshot · Link · Journey · JourneyPage · Token · Asset · Component · ComponentProperty · ComponentState · Element What capture finds and what is made in Babul — the base. A snapshot per page × size × state; links with a label and action; journeys as named page sets; tokens by group; icons, logos and images; components with their properties and their seven states (made, not made, not needed); elements drawn in Babul
Versioning (7) Ref · Version · Commit · Draft · Change · Value · Conflict main and branches; versions (v0.0.1, then one per merge, with its bump); commits; one private draft per person per ref; one Change row per property, in a draft or a commit; each ref's current values, kept ready; conflicts with Take main or Keep branch
Verify and Analysis (4) VerifyRun · VerifyPage · AnalysisRun · Finding A run per branch and URL with a 0–100 score per page and size; three scores and ranked findings per capture
AI and money (3) AiAction · Subscription · PaymentEvent One row per AI call for the meter (outlives what it points at); the company's Polar subscription; each Polar webhook once
Sharing and limits (2) PreviewLink · RateLimit Preview links that expire or are turned off; counters shared by every server

What changes from today's schema

  • Gone or merged: the Kit and its releases; Interaction; StyleSection; AssetUse and ComponentUsage (where-used is read from the snapshots); LighthouseResult (axe-core findings are Findings); Invoice and BillingProfile (Polar keeps them); Tag (a version is a Version row); Import (an import is draft rows); CodePreference; PageRole (a column on Page); RoleCredential (merged into ProductRole); CaptureRun (now Capture plus Job); DrawnElement (now Element).
  • New: Job, one queue for every slow task; Version with its bump; ComponentState (not made · not needed); Invite and Membership.lastSeenAt for "who is here".
  • Kept as they work today: every company row carries clientId; children's foreign keys include clientId and productId; row-level security per company and per chosen product; sealed role passwords readable only by the worker; checks and triggers at the bottom of the migration.

How a change is stored (unchanged in spirit, simpler in shape): an edit writes a Change (target, property, value, the value before) into the person's Draft. Review moves the draft's rows to a new Commit on a branch and rebuilds that ref's Value rows. A merge applies the branch's rows to main, writes a Version, and syncs every other branch: a property changed on both sides to different values becomes a Conflict. A captured element needs no row; a change names it as <pageId>#<selector>.

Why

The redesign (T061, features 0025–0036) needs fewer, plainer tables: the Kit, interactions, sections and tags are gone from the screens, and Polar keeps billing records. One job queue replaces per-task run tables. The tenant rules stay exactly as they are, because they are tested and they are what keeps one company from seeing another's data.

Impact

  • schema.prisma is this schema; the migration is written again; dev and test databases are dropped and reseeded on the Northwind example.
  • The rebuild (T061) deletes the code for the tables that are gone.
  • tenant-isolation stays true; its tests move to the new tables.

Status

Active. Approved by the owner on 2026-10-23, with: no second copy of anything, and old code that no longer fits is deleted, not kept.

Sources