Requirement & Relationship Schema (Drizzle base + 007–023)

ELI5

A requirement is a sticky note in a workspace, and every workspace belongs to one tenant (the paying customer). A person signs in as a row in BetterAuth’s user table. That row points at the workspace they are working in now, and the workspace points at its tenant. Links between sticky notes are stored once, in the direction you drew them.

Technical Deep Dive

Source: traceo-cat/migrations/README.md:9-40, migrations/000_drizzle_base.sql:65-109, migrations/008_reconcile_schema.sql:27-200, migrations/016_extend_requirements.sql:25-34, migrations/018_add_verifies_relationship.sql:7, migrations/019_derive_tenant_claim_from_subject.sql, traceo_mcp_server/traceo_mcp_server/services/relationships.py:1-40 and :240-290, services/traceability.py:13-27; decisions 0027, 0107, 0108; traceo-cat origin/main 7d647ab, read 2026-10-25

The apply order is not 001…N

migrations/README.md says it bluntly: migrations 001–006 were written against a plural schema (tenants, workspaces, users) that was never deployed. They now sit in migrations/legacy/. The canonical from-scratch path is 000_drizzle_base.sql (generated by Drizzle from the web client’s schema), then 007 through 023 in order. The v1.0 card described the legacy 001 tables. Those tables do not exist.

Production’s applied set is a separate fact from this file list. Decision 0095 recorded production on 2026-09-09 as legacy 001–008 plus 016/017/018. 021, 010 and 020 were hand-applied on 2026-09-11, and MILESTONES.md M-21 records 022 as evidenced only in an unmerged PR as of 2026-10-11. Do not assume production equals 000→023.

The tenant path

There is no tenant_id on "user". A subject reaches a tenant through "user".workspace_id → workspace.tenant_id, which is the join 019 encodes and the web client’s billing actor uses (lib/billing/actor.ts). Decision 0107 (accepted, not yet built) rules that a tenant_member (user_id, tenant_id, role) table must exist before any invite ships, with "user".workspace_id demoted to an active-workspace pointer. No migration for it exists in 007–023.

erDiagram
  TENANT ||--o{ WORKSPACE : owns
  WORKSPACE ||--o{ USER : "active workspace (user.workspace_id)"
  WORKSPACE ||--o{ REQUIREMENTS : scopes
  WORKSPACE ||--o{ RELATIONSHIPS : scopes
  REQUIREMENTS ||--o{ RELATIONSHIPS : "source_id / target_id"
  REQUIREMENTS ||--o{ REQUIREMENT_VERSIONS : "versioned by"
  USER ||--o{ SESSION : has

Requirement enums (008)

requirements.external_id (the human REQ-… identifier) is unique per workspace among rows where deleted_at IS NULL.

Relationships: one row, stored direction

relationship_type holds the 15 values from 008 plus derives_from (016) and verifies (018), 17 in total. relationships is unique on (source_id, target_id, relationship_type) and has CHECK (source_id != target_id).

Writes go through services/relationships.py (decision 0108), the one path both MCP (create_relationship / delete_relationship) and REST use. It writes one row. It validates the type against the database’s own enum, refuses self-links, returns the existing edge on a duplicate, and requires both endpoints to sit in the caller’s own workspace. Decision 0122 makes the UI read the stored direction rather than infer an inverse.

services/traceability.py still carries a _REVERSE_RELATIONSHIPS map and a TraceabilityLinkService.add_link that writes a forward and a reverse row. A positive search on 2026-10-25 found it re-exported from services/__init__.py and called nowhere else. It also maps derives_from → derived_by, and derived_by is not a value of the database enum.

flowchart TD
  C["create_relationship (MCP) or POST (REST)"] --> W{"caller role editor/admin/owner?"}
  W -- no --> R1["refused"]
  W -- yes --> T{"type in DB enum? endpoints in caller's workspace? not a self-link?"}
  T -- no --> R2["refused, foreign endpoint answered as missing"]
  T -- yes --> I["INSERT one row as traceo_app"]
  I --> P{"RLS members_create_relationships"}
  P -- pass --> OK["edge stored in the drawn direction"]
  P -- fail --> R3["InsufficientPrivilege"]

Key Terms

• Drizzle base → 000_drizzle_base.sql, generated from the web client’s Drizzle schema. It is the root of the apply path.
• Legacy stratum → migrations/legacy/001–006, the plural schema that was never deployed.
• Subject tenant → the tenant reached by "user".workspace_id → workspace.tenant_id, which auth.subject_tenant_id() returns.
• Stored direction → a relationship is one row, read as drawn. Nothing on the live path auto-writes an inverse.

Q&A

Q: Through which columns does a signed-in user reach a tenant? A: "user".workspace_id → workspace.tenant_id. "user" has no tenant_id column (000_drizzle_base.sql:75-89), and 019 encodes exactly this join.

Q: Why must you never apply migrations 001–006? A: They target plural tables (tenants, workspaces, users) that were never deployed. 008 reconciles against the Drizzle base instead. The README’s apply order is 000, then 007 onward.

Q: Does creating A implements B also write B implemented_by A today? A: No. The live write path (services/relationships.py, decision 0108) inserts one row. The reverse-map helper in services/traceability.py has no caller.

Q: Which migrations added derives_from and verifies to the relationship enum? A: 016_extend_requirements.sql added derives_from (the value the sync emits), and 018_add_verifies_relationship.sql added verifies (CHUM sync coverage edges).

Examples

An editor calls create_relationship(source="REQ-1", target="REQ-2", relationship_type="verifies"). The service resolves both external ids inside the caller’s workspace, checks that verifies is in the enum (it has been since 018), and inserts one row. A later trace_requirement("REQ-2") finds the edge as inbound. No verified_by row was written.