Traceo Database Schema

This document defines the PostgreSQL database schema for Traceo, designed for multi-tenant SaaS deployment with Supabase.

Design Principles

Principle	Implementation
Multi-tenancy	Row-Level Security (RLS) on `workspace_id`
Primary Keys	UUID v7 (sortable, distributed-safe)
Timestamps	`timestamptz` (timezone-aware)
Flexible Data	JSONB for metadata and extensible fields
Audit Trail	Full versioning with change tracking
Soft Deletes	`deleted_at` column where applicable

Entity Relationship Diagram

┌─────────────┐       ┌─────────────────┐       ┌──────────────────┐
│   tenants   │──────<│   workspaces    │──────<│      users       │
└─────────────┘       └─────────────────┘       └──────────────────┘
                              │                          │
                              │                          │
              ┌───────────────┼───────────────┐          │
              │               │               │          │
              ▼               ▼               ▼          ▼
      ┌──────────────┐ ┌─────────────┐ ┌────────────┐ ┌────────────┐
      │ requirements │ │ingestion_  │ │mcp_sessions│ │audit_logs  │
      └──────────────┘ │   jobs     │ └────────────┘ └────────────┘
              │        └─────────────┘
              │               │
      ┌───────┴───────┐       │
      │               │       ▼
      ▼               ▼  ┌────────────┐
┌──────────────┐ ┌──────────────────┐
│relationships │ │requirement_      │ │job_issues  │
└──────────────┘ │   versions       │ └────────────┘
                 └──────────────────┘

Tables

Core Multi-Tenancy

`tenants`

Top-level organization entity for multi-tenant isolation.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Tenant identifier
`name`	`text`	NOT NULL	Organization name
`slug`	`text`	UNIQUE, NOT NULL	URL-safe identifier
`settings`	`jsonb`	DEFAULT `'{}'`	Tenant-level settings
`plan`	`text`	DEFAULT `'free'`	Subscription plan
`created_at`	`timestamptz`	DEFAULT `now()`	Creation timestamp
`updated_at`	`timestamptz`	DEFAULT `now()`	Last update

`workspaces`

Isolated workspace within a tenant. Primary RLS boundary.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Workspace identifier
`tenant_id`	`uuid`	FK → tenants, NOT NULL	Parent tenant
`name`	`text`	NOT NULL	Workspace name
`slug`	`text`	NOT NULL	URL-safe identifier
`description`	`text`		Workspace description
`settings`	`jsonb`	DEFAULT `'{}'`	Workspace settings
`created_at`	`timestamptz`	DEFAULT `now()`	Creation timestamp
`updated_at`	`timestamptz`	DEFAULT `now()`	Last update

Unique Constraint: (tenant_id, slug)

`users`

User accounts linked to Supabase Auth.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	User identifier
`workspace_id`	`uuid`	FK → workspaces, NOT NULL	Primary workspace
`supabase_uid`	`uuid`	UNIQUE, NOT NULL	Supabase Auth user ID
`email`	`text`	NOT NULL	Email address
`display_name`	`text`		Display name
`role`	`text`	NOT NULL, DEFAULT `'member'`	Role: owner, admin, member, viewer
`settings`	`jsonb`	DEFAULT `'{}'`	User preferences
`created_at`	`timestamptz`	DEFAULT `now()`	Creation timestamp
`updated_at`	`timestamptz`	DEFAULT `now()`	Last update
`last_login_at`	`timestamptz`		Last login timestamp

Requirements Management

`requirements`

Core requirements table with full traceability support.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Internal identifier
`workspace_id`	`uuid`	FK → workspaces, NOT NULL	Workspace scope
`external_id`	`text`	NOT NULL	Human-readable ID (e.g., `BR-001`)
`title`	`text`	NOT NULL	Requirement title
`description`	`text`		Full description
`type`	`requirement_type`	NOT NULL	Enum: business, functional, etc.
`classification`	`requirement_classification`		5-layer hierarchy level
`status`	`requirement_status`	NOT NULL, DEFAULT `'draft'`	Lifecycle status
`priority`	`requirement_priority`	DEFAULT `'P2-medium'`	Priority level
`acceptance_criteria`	`jsonb`	DEFAULT `'[]'`	List of acceptance criteria
`success_criteria`	`jsonb`	DEFAULT `'[]'`	Business success metrics
`stakeholders`	`jsonb`	DEFAULT `'{}'`	Stakeholder information
`metadata`	`jsonb`	DEFAULT `'{}'`	Extensible metadata
`version`	`integer`	NOT NULL, DEFAULT `1`	Current version number
`created_by`	`uuid`	FK → users	Creator
`updated_by`	`uuid`	FK → users	Last updater
`created_at`	`timestamptz`	DEFAULT `now()`	Creation timestamp
`updated_at`	`timestamptz`	DEFAULT `now()`	Last update
`deleted_at`	`timestamptz`		Soft delete timestamp

Unique Constraint: (workspace_id, external_id) WHERE deleted_at IS NULL

`relationships`

Traceability relationships between requirements.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Relationship identifier
`workspace_id`	`uuid`	FK → workspaces, NOT NULL	Workspace scope
`source_id`	`uuid`	FK → requirements, NOT NULL	Source requirement
`target_id`	`uuid`	FK → requirements, NOT NULL	Target requirement
`relationship_type`	`relationship_type`	NOT NULL	Type of relationship
`metadata`	`jsonb`	DEFAULT `'{}'`	Additional context
`created_by`	`uuid`	FK → users	Creator
`created_at`	`timestamptz`	DEFAULT `now()`	Creation timestamp

Unique Constraint: (source_id, target_id, relationship_type) Check Constraint: source_id != target_id

`requirement_versions`

Full version history for requirements.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Version identifier
`requirement_id`	`uuid`	FK → requirements, NOT NULL	Parent requirement
`version`	`integer`	NOT NULL	Version number
`snapshot`	`jsonb`	NOT NULL	Full requirement state
`changes`	`jsonb`	DEFAULT `'{}'`	Diff from previous version
`change_reason`	`text`		Reason for change
`changed_by`	`uuid`	FK → users	User who made change
`created_at`	`timestamptz`	DEFAULT `now()`	Version timestamp

Unique Constraint: (requirement_id, version)

Ingestion Pipeline

`ingestion_jobs`

Track CSV/file ingestion jobs.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Job identifier
`workspace_id`	`uuid`	FK → workspaces, NOT NULL	Workspace scope
`status`	`job_status`	NOT NULL, DEFAULT `'pending'`	Job status
`source_type`	`text`	NOT NULL	Source: csv, excel, yaml
`source_filename`	`text`	NOT NULL	Original filename
`source_size_bytes`	`bigint`		File size
`total_rows`	`integer`		Total rows to process
`processed_rows`	`integer`	DEFAULT `0`	Rows processed
`created_rows`	`integer`	DEFAULT `0`	New requirements created
`updated_rows`	`integer`	DEFAULT `0`	Requirements updated
`error_rows`	`integer`	DEFAULT `0`	Rows with errors
`options`	`jsonb`	DEFAULT `'{}'`	Job options
`result`	`jsonb`		Final result summary
`created_by`	`uuid`	FK → users	User who started job
`started_at`	`timestamptz`		Processing start time
`completed_at`	`timestamptz`		Processing end time
`created_at`	`timestamptz`	DEFAULT `now()`	Job creation time

`job_issues`

Issues encountered during ingestion.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Issue identifier
`job_id`	`uuid`	FK → ingestion_jobs, NOT NULL	Parent job
`severity`	`text`	NOT NULL	error, warning, info
`issue_type`	`text`	NOT NULL	Category of issue
`message`	`text`	NOT NULL	Human-readable message
`row_number`	`integer`		CSV row number
`column_name`	`text`		Column that caused issue
`raw_value`	`text`		Original value
`created_at`	`timestamptz`	DEFAULT `now()`	Issue timestamp

Session & Audit

`mcp_sessions`

Track MCP protocol sessions for analytics.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Session identifier
`workspace_id`	`uuid`	FK → workspaces, NOT NULL	Workspace scope
`user_id`	`uuid`	FK → users	Associated user
`client_info`	`jsonb`	DEFAULT `'{}'`	Client metadata
`tool_calls`	`integer`	DEFAULT `0`	Number of tool calls
`requirements_created`	`integer`	DEFAULT `0`	Requirements created
`requirements_updated`	`integer`	DEFAULT `0`	Requirements updated
`started_at`	`timestamptz`	DEFAULT `now()`	Session start
`ended_at`	`timestamptz`		Session end
`last_activity_at`	`timestamptz`	DEFAULT `now()`	Last activity

`audit_logs`

Comprehensive audit trail for compliance.

Column	Type	Constraints	Description
`id`	`uuid`	PK, DEFAULT `gen_random_uuid()`	Log identifier
`workspace_id`	`uuid`	FK → workspaces, NOT NULL	Workspace scope
`user_id`	`uuid`	FK → users	Acting user
`action`	`text`	NOT NULL	Action: create, update, delete, export
`resource_type`	`text`	NOT NULL	Table/entity name
`resource_id`	`uuid`		Affected resource
`changes`	`jsonb`	DEFAULT `'{}'`	Before/after snapshot
`ip_address`	`inet`		Client IP
`user_agent`	`text`		Client user agent
`created_at`	`timestamptz`	DEFAULT `now()`	Log timestamp

Enum Types

-- Requirement type classifications
CREATE TYPE requirement_type AS ENUM (
    'business_requirement',
    'functional_requirement',
    'non_functional_requirement',
    'system_requirement',
    'implementation_requirement',
    'verification_requirement'
);

-- 5-layer hierarchy classifications
CREATE TYPE requirement_classification AS ENUM (
    'L1-Strategic',
    'L2-Functional',
    'L2-Performance',
    'L3-System',
    'L4-Implementation',
    'L4-Verification'
);

-- Requirement priority levels
CREATE TYPE requirement_priority AS ENUM (
    'P0-critical',
    'P1-high',
    'P2-medium',
    'P3-low'
);

-- Requirement status lifecycle
CREATE TYPE requirement_status AS ENUM (
    'draft',
    'candidate',
    'approved',
    'active',
    'implemented',
    'verified',
    'deprecated'
);

-- 6-category traceability relationship types
CREATE TYPE relationship_type AS ENUM (
    'implements',
    'satisfies',
    'decomposes_into',
    'supports',
    'depends_on',
    'conflicts_with',
    'relates_to',
    'verified_by',
    'validates',
    'constrains',
    'constrained_by',
    'affects',
    'affected_by',
    'realized_by',
    'implemented_by'
);

-- Ingestion job status
CREATE TYPE job_status AS ENUM (
    'pending',
    'processing',
    'completed',
    'failed',
    'cancelled'
);

Row-Level Security (RLS)

All tables containing workspace data enforce RLS. The core pattern:

-- Users can only access their workspace's data
CREATE POLICY "workspace_isolation" ON requirements
  FOR ALL
  USING (
    workspace_id IN (
      SELECT workspace_id FROM users 
      WHERE supabase_uid = auth.uid()
    )
  );

See migrations/002_rls_policies.sql for complete RLS implementation.

Indexes

Performance indexes are defined in migrations/003_indexes.sql:

Table	Index	Columns	Type
`requirements`	`idx_requirements_workspace`	`workspace_id`	B-tree
`requirements`	`idx_requirements_external_id`	`workspace_id, external_id`	B-tree
`requirements`	`idx_requirements_type`	`workspace_id, type`	B-tree
`requirements`	`idx_requirements_status`	`workspace_id, status`	B-tree
`requirements`	`idx_requirements_search`	`title, description`	GIN (full-text)
`relationships`	`idx_relationships_source`	`source_id`	B-tree
`relationships`	`idx_relationships_target`	`target_id`	B-tree
`relationships`	`idx_relationships_type`	`relationship_type`	B-tree
`audit_logs`	`idx_audit_workspace_time`	`workspace_id, created_at DESC`	B-tree
`ingestion_jobs`	`idx_jobs_workspace_status`	`workspace_id, status`	B-tree

JSONB Column Schemas

`requirements.acceptance_criteria`

[
  {
    "id": "AC-001",
    "description": "User can log in with email/password",
    "status": "verified",
    "test_ref": ["TC-AUTH-001", "TC-AUTH-002"]
  }
]

`requirements.success_criteria`

[
  {
    "metric": "API Response Time (P95)",
    "target": "<50ms",
    "current": "45ms",
    "status": "target_met",
    "business_impact": "Improved user experience",
    "measurement_method": "Prometheus monitoring"
  }
]

`requirements.stakeholders`

{
  "business_owner": "Jane Smith",
  "technical_owner": "John Doe",
  "approvers": ["VP Engineering", "Product Lead"],
  "code_reviewers": ["senior-dev-1", "senior-dev-2"]
}

Ariel Tables

Ariel baseline management tables are created by migrations/005_ariel_baselines.sql. All tables use RLS policies scoped to workspace_id.

`ariel_baselines`

Stores baseline metadata. Each baseline belongs to a workspace and follows a lifecycle: draft → frozen → superseded or archived.

Column	Type	Description
`id`	`UUID`	Primary key
`workspace_id`	`UUID`	FK → `workspaces.id`
`baseline_id`	`TEXT`	Human-readable ID (e.g., `BL-ARCH-001`)
`name`	`TEXT`	Display name
`version`	`TEXT`	Semantic version (default `1.0.0`)
`status`	`baseline_status`	`draft`, `frozen`, `superseded`, `archived`
`author`	`TEXT`	Creator identifier
`description`	`TEXT`	Optional description
`domain`	`TEXT`	Classification domain
`frozen_at`	`TIMESTAMPTZ`	When the baseline was frozen
`git_ref`	`TEXT`	Git commit SHA at freeze time
`parent_baseline`	`TEXT`	ID of the baseline this supersedes
`created_at`	`TIMESTAMPTZ`	Row creation time
`updated_at`	`TIMESTAMPTZ`	Last update time

Unique constraint: (workspace_id, baseline_id)

`ariel_configuration_items`

Configuration items (CIs) belonging to a baseline. Each CI references a document, code snippet, or diagram.

Column	Type	Description
`id`	`UUID`	Primary key
`baseline_id`	`UUID`	FK → `ariel_baselines.id` (CASCADE)
`type`	`TEXT`	CI type: `doc`, `snippet`, `diagram`, `spec`, `test`
`ref`	`TEXT`	File path or reference
`hash`	`TEXT`	SHA-256 content hash (set on freeze)
`metadata`	`JSONB`	Additional metadata
`created_at`	`TIMESTAMPTZ`	Row creation time

Unique constraint: (baseline_id, type, ref)

`ariel_traceo_references`

Links between baseline CIs and Traceo requirements. Created during sync.

Column	Type	Description
`id`	`UUID`	Primary key
`baseline_id`	`UUID`	FK → `ariel_baselines.id` (CASCADE)
`traceo_id`	`TEXT`	Traceo requirement ID (e.g., `SR-ARCH-001`)
`relationship`	`TEXT`	Link type: `implements`, `satisfies`, `traces_to`, `derived_from`
`created_at`	`TIMESTAMPTZ`	Row creation time

Unique constraint: (baseline_id, traceo_id, relationship)

`ariel_sync_history`

Audit log of sync operations between Ariel baselines and Traceo requirements.

Column	Type	Description
`id`	`UUID`	Primary key
`workspace_id`	`UUID`	FK → `workspaces.id`
`baseline_id`	`TEXT`	Baseline that was synced
`direction`	`TEXT`	`baseline_to_traceo` or `traceo_to_baseline`
`status`	`TEXT`	`started`, `completed`, `failed`
`details`	`JSONB`	Sync results, error info, counts
`created_at`	`TIMESTAMPTZ`	When the sync occurred

RLS Policies

All Ariel tables enforce workspace isolation via RLS:

SELECT: Users can only read rows matching their workspace_id (via auth.uid())
INSERT: workspace_id must match the user’s workspace
UPDATE/DELETE: Only rows in the user’s workspace can be modified

Indexes

Key indexes for query performance:

ariel_baselines: (workspace_id, status), (workspace_id, domain), (workspace_id) WHERE status = 'frozen'
ariel_configuration_items: (baseline_id), (type, ref)
ariel_traceo_references: (baseline_id), (traceo_id)
ariel_sync_history: (workspace_id, created_at DESC), (baseline_id)

Migration Strategy

Initial Schema (001_initial_schema.sql): Creates all tables and types
RLS Policies (002_rls_policies.sql): Enables security policies
Indexes (003_indexes.sql): Adds performance indexes
Future Migrations: Incremental changes with rollback support
Ariel Baselines (005_ariel_baselines.sql): Ariel baseline management tables with RLS

See /migrations/README.md for migration procedures.

Guides

API

Operations

Database Schema

Traceo Database Schema

Design Principles

Entity Relationship Diagram

Tables

Core Multi-Tenancy

`tenants`

`workspaces`

`users`

Requirements Management

`requirements`

`relationships`

`requirement_versions`

Ingestion Pipeline

`ingestion_jobs`

`job_issues`

Session & Audit

`mcp_sessions`

`audit_logs`

Enum Types

Row-Level Security (RLS)

Indexes

JSONB Column Schemas

`requirements.acceptance_criteria`

`requirements.success_criteria`

`requirements.stakeholders`

Ariel Tables

`ariel_baselines`

`ariel_configuration_items`

`ariel_traceo_references`

`ariel_sync_history`

RLS Policies

Indexes

Migration Strategy

Guides

API

Operations

​Traceo Database Schema

​Design Principles

​Entity Relationship Diagram

​Tables

​Core Multi-Tenancy

​tenants

​workspaces

​users

​Requirements Management

​requirements

​relationships

​requirement_versions

​Ingestion Pipeline

​ingestion_jobs

​job_issues

​Session & Audit

​mcp_sessions

​audit_logs

​Enum Types

​Row-Level Security (RLS)

​Indexes

​JSONB Column Schemas

​requirements.acceptance_criteria

​requirements.success_criteria

​requirements.stakeholders

​Ariel Tables

​ariel_baselines

​ariel_configuration_items

​ariel_traceo_references

​ariel_sync_history

​RLS Policies

​Indexes

​Migration Strategy

Traceo Database Schema

Design Principles

Entity Relationship Diagram

Tables

Core Multi-Tenancy

`tenants`

`workspaces`

`users`

Requirements Management

`requirements`

`relationships`

`requirement_versions`

Ingestion Pipeline

`ingestion_jobs`

`job_issues`

Session & Audit

`mcp_sessions`

`audit_logs`

Enum Types

Row-Level Security (RLS)

Indexes

JSONB Column Schemas

`requirements.acceptance_criteria`

`requirements.success_criteria`

`requirements.stakeholders`

Ariel Tables

`ariel_baselines`

`ariel_configuration_items`

`ariel_traceo_references`

`ariel_sync_history`

RLS Policies

Indexes

Migration Strategy