Data model overview

Postgres via Drizzle; every tenant-owned table carries workspace_id and every repository method takes it as a required first argument — queries physically cannot forget the tenancy filter (DATA_ACCESS).

Core spine:

user ─ workspace_member ─ workspace ─ project ─ work_item ─ comment / attachment / activity
                                        │           └ parent_id (one-level sub-tasks)
                                        ├ workflow_state (categories: backlog/unstarted/started/completed/cancelled)
                                        ├ cycle (date-only timeboxes)
                                        └ saved_view
workspace ─ objective / module (roadmap) · notification · audit_event · outbox_event · email_outbox · idempotency_key

Semantics worth knowing before changing anything:

• Identifiers (SKY-42) mint from project.next_item_seq under lock — unique, sequential, immutable.

• Revisions: work_item.revision backs compare-and-set updates (409 with the authoritative body on staleness). Same contract on cycles.

• Soft deletes (deleted_at) on items/comments; listings always filter them.

• Activity + outbox rows commit in the mutation’s transaction — proven by fault-injection tests.

• Migrations live in packages/db; CI runs both fresh-install and upgrade paths.

• Knowledge pages (PR-83) store a structured document, not HTML: content_json is the source of truth and content_text is a server-extracted search projection — the client never supplies it, so the two cannot drift. Every block carries a blockId normalized server-side, which is what lets a task created from a paragraph survive later edits to it. Depth is computed, never stored; a move would leave a stored depth wrong. History is an append-only snapshot table with a unique (page_id, revision) index.

Full model: DATA_MODEL.