Schema

Decision

Canon Cloud's Postgres schema is packages/db/prisma/schema.prisma, in three parts:

• Access: User (Better Auth), Workspace (TEAM or PRIVATE; plan TEAM or ENTERPRISE), Membership (Owner, Reviewer, Member, Viewer; optionally limited to spaces), Invite.
• An index of main, rebuilt from GitHub: Installation, Space, Repo, Page, PageLink, PullRequest, Reviewer, SpaceMetrics. Safe to drop and resync.
• What has no home in git: Draft, CommentThread and Comment, Watch, Notification, Capture, Integration, AuditEvent, Subscription.

Every workspace row carries workspaceId, child keys include it, and row-level security hides other workspaces (babul's withMember).

Why

• The repo stays the only truth for docs, so no table is a second copy people edit. Pages change only by sync.
• Drafts live here, not as a branch per draft: saving as you type would flood git. Publish opens the branch and PR.
• A draft and a comment thread name a doc by space and path, so a resync never deletes someone's work.
• Editors are counted from roles, never stored, so the bill cannot drift. Gate approvals stay in gates.jsonl in the repo.

Rejected

• Better Auth's organization plugin for tenants: babul's own tables give composite keys and row-level security.
• Doc bodies as the source of truth here: two truths drift.

Impact

packages/db holds it: one migration with the SQL checks and row-level security, and withWorkspace, withMember and withPerson. Its tests run against Postgres 17 (docker compose up -d).