Evolith Tracker — PostgreSQL Data Design

Bilingual Navigation: English (this document) · Versión en Español

Document Status: Draft
Type: Data Architecture Design
Satellite: Evolith Tracker
Upstream: Evolith Core
Reference Implementation: UMS (technical patterns only)
Date: 2026-06-07
Author: Architect Agent (BMAD)

1. Schema Architecture

1.1 Schema Map

Schema	Bounded Context	Owner Module	Purpose
`tracker_discovery`	Discovery	`@evolith/tracker-discovery`	Initiative, Backlog, Epic, UserStory aggregates
`tracker_design`	Design	`@evolith/tracker-design`	TechnicalBlueprint, Contract, ADR, DataSchema
`tracker_construction`	Construction	`@evolith/tracker-construction`	ImplementationCycle, TechnicalStory, PeerReview
`tracker_qa`	QA	`@evolith/tracker-qa`	TestCycle, TestExecution, Defect
`tracker_release`	Release	`@evolith/tracker-release`	ReleasePackage, DeploymentRecord, Environment
`tracker_governance`	Governance	`@evolith/tracker-governance`	SatelliteProduct, SDLCExecution, PhaseGateState, ExceptionRequest
`tracker_artifacts`	Artifacts	`@evolith/tracker-artifacts`	ArtifactDefinition, ArtifactInstance, EvidenceRecord
`tracker_metrics`	Metrics	`@evolith/tracker-metrics`	ScorecardDefinition, MetricSnapshot, DriftAlert
`tracker_integration`	Integration	`@evolith/tracker-integration`	IntegrationEndpoint, SyncRecord, ACLAdapter
`tracker_audit`	Audit	`@evolith/tracker-audit`	AuditEntry (append-only)

1.2 Cross-Schema Reference Strategy

No foreign keys between schemas. Cross-context references use UUIDs only. The application layer enforces referential integrity.

-- Example: QA referencing Construction (cross-schema UUID reference)
CREATE TABLE tracker_qa.test_cycles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  implementation_cycle_id UUID NOT NULL,  -- References tracker_construction.implementation_cycles.id
  tenant_id UUID NOT NULL,
  -- No FK constraint. Application layer validates existence.
  target_version TEXT NOT NULL,
  start_date TIMESTAMPTZ NOT NULL,
  end_date TIMESTAMPTZ,
  pass_rate_percentage FLOAT,
  status TEXT NOT NULL DEFAULT 'active',
  qa_lead_id UUID NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL
);

2. Shared Patterns

2.1 Common Columns (Implicit on All Tables)

Every table includes:

Column	Type	Constraint	Purpose
`id`	`UUID`	`PRIMARY KEY DEFAULT gen_random_uuid()`	Global unique identity
`tenant_id`	`UUID`	`NOT NULL`	Multi-tenancy isolation
`created_at`	`TIMESTAMPTZ`	`NOT NULL DEFAULT now()`	Audit timestamp (UTC)
`created_by`	`UUID`	`NOT NULL`	User who created the record
`updated_at`	`TIMESTAMPTZ`	`NOT NULL DEFAULT now()`	Last modification timestamp
`updated_by`	`UUID`	`NOT NULL`	User who last modified
`deleted_at`	`TIMESTAMPTZ`	`NULL`	Soft delete marker (NULL = active)

2.2 Audit Columns Value Objects (Embedded)

These are not separate tables — they are embedded as JSONB columns in domain aggregates that need flexible audit trail:

-- RequirementChecklist as JSONB
-- RequirementItem as JSONB
-- StateTransition as JSONB
-- AuditControl as JSONB (simplified, on every table via shared columns)

2.3 Concurrency Control

-- Optimistic concurrency via xmin system column (PostgreSQL MVCC)
-- Read: SELECT xmin, * FROM table WHERE id = ?;
-- Write: UPDATE table SET ... WHERE id = ? AND xmin = ?;

-- For entities requiring explicit version field:
ALTER TABLE tracker_discovery.initiatives ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Conflict detection in application layer
-- If updated row's xmin differs from the version stored in the aggregate, throw ConcurrencyError

2.4 Index Conventions

-- Tenant-scoped queries: composite index on (tenant_id, id) — most common access pattern
CREATE INDEX idx_<table>_tenant_id_id ON <schema>.<table> (tenant_id, id);

-- Status filtering: composite on (tenant_id, status, created_at)
CREATE INDEX idx_<table>_tenant_status_created ON <schema>.<table> (tenant_id, status, created_at DESC);

-- Soft delete: partial index for active records only
CREATE INDEX idx_<table>_active ON <schema>.<table> (tenant_id) WHERE deleted_at IS NULL;

-- Full-text search (for artifact names, descriptions)
CREATE INDEX idx_<table>_fts ON <schema>.<table> USING gin(to_tsvector('english', name || ' ' || description));

3. Schema: tracker_discovery

3.1 initiatives

CREATE SCHEMA IF NOT EXISTS tracker_discovery;

CREATE TABLE tracker_discovery.initiatives (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'submitted', 'under_review', 'approved', 'rejected', 'backlog_generated')),
  strategic_theme TEXT,
  target_quarter TEXT,

  -- Ownership
  sponsor_id UUID NOT NULL,                    -- UMS user ID
  authorized_group_ids UUID[] NOT NULL DEFAULT '{}',

  -- Canvas (JSONB — Discovery Canvas data as structured JSON)
  canvas_data JSONB NOT NULL DEFAULT '{}',

  -- Requirement Checklist (JSONB — dynamic workflow steps)
  requirement_checklist JSONB NOT NULL DEFAULT '{"items": []}',

  -- Approval gate (JSONB — embedded VO)
  approval_gate JSONB,

  -- Cross-references (not FKs)
  blueprint_id UUID,                           -- Will reference tracker_design.technical_blueprints.id

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,

  -- Optimistic concurrency
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_initiatives_tenant_status ON tracker_discovery.initiatives (tenant_id, status, created_at DESC);
CREATE INDEX idx_initiatives_tenant_sponsor ON tracker_discovery.initiatives (tenant_id, sponsor_id);
CREATE INDEX idx_initiatives_fts ON tracker_discovery.initiatives USING gin(to_tsvector('english', title || ' ' || description));

3.2 backlogs

CREATE TABLE tracker_discovery.backlogs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  initiative_id UUID NOT NULL,

  -- Ordered story IDs (array of UUIDs — Small Aggregates pattern, not nested objects)
  ordered_story_ids UUID[] NOT NULL DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_backlogs_tenant_initiative ON tracker_discovery.backlogs (tenant_id, initiative_id);

3.3 epics

CREATE TABLE tracker_discovery.epics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  backlog_id UUID NOT NULL,

  -- Core fields
  title TEXT NOT NULL,
  description TEXT,
  priority TEXT NOT NULL DEFAULT 'medium'
    CHECK (priority IN ('critical', 'high', 'medium', 'low')),
  assignee_id UUID,                            -- UMS user ID
  reporter_id UUID NOT NULL,

  -- External references (JSONB array)
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_epics_tenant_backlog ON tracker_discovery.epics (tenant_id, backlog_id);
CREATE INDEX idx_epics_assignee ON tracker_discovery.epics (tenant_id, assignee_id);

3.4 user_stories

CREATE TABLE tracker_discovery.user_stories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  epic_id UUID NOT NULL,

  -- Core fields
  description TEXT NOT NULL,
  acceptance_criteria TEXT,
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'in_refinement', 'split', 'ready', 'in_progress', 'done')),
  priority TEXT NOT NULL DEFAULT 'medium'
    CHECK (priority IN ('critical', 'high', 'medium', 'low')),
  story_points INTEGER,
  assignee_id UUID,
  reporter_id UUID NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}',
  due_date TIMESTAMPTZ,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_user_stories_tenant_epic ON tracker_discovery.user_stories (tenant_id, epic_id);
CREATE INDEX idx_user_stories_assignee ON tracker_discovery.user_stories (tenant_id, assignee_id);
CREATE INDEX idx_user_stories_status ON tracker_discovery.user_stories (tenant_id, status);

4. Schema: tracker_design

4.1 technical_blueprints

CREATE SCHEMA IF NOT EXISTS tracker_design;

CREATE TABLE tracker_design.technical_blueprints (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  initiative_id UUID NOT NULL,                 -- Cross-schema: tracker_discovery.initiatives.id

  -- Core fields
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'in_review', 'approved', 'rejected')),
  version TEXT NOT NULL DEFAULT '1.0.0',

  -- Ownership
  author_id UUID NOT NULL,
  reviewer_ids UUID[] NOT NULL DEFAULT '{}',

  -- Content references (not FKs — referencing other aggregates in this schema)
  contract_ids UUID[] NOT NULL DEFAULT '{}',
  adr_ids UUID[] NOT NULL DEFAULT '{}',
  schema_ids UUID[] NOT NULL DEFAULT '{}',
  epic_ids UUID[] NOT NULL DEFAULT '{}',

  -- Tags
  tags TEXT[] NOT NULL DEFAULT '{}',

  -- Requirement Checklist
  requirement_checklist JSONB NOT NULL DEFAULT '{"items": []}',

  -- Approval gate
  architecture_gate JSONB,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_blueprints_tenant_initiative ON tracker_design.technical_blueprints (tenant_id, initiative_id);
CREATE INDEX idx_blueprints_tenant_status ON tracker_design.technical_blueprints (tenant_id, status);

4.2 technical_contracts

CREATE TABLE tracker_design.technical_contracts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  blueprint_id UUID NOT NULL,

  -- Core fields
  contract_type TEXT NOT NULL                 -- Phase 1: 'openapi', 'asyncapi' (REST-only, see T-009)
    CHECK (contract_type IN ('openapi', 'asyncapi', 'other')),
                                              -- 'graphql'/'grpc' reserved for Phase 2 — add via migration upon ADR approval
  content_payload TEXT NOT NULL,              -- The actual spec content (YAML/JSON string)
  format TEXT NOT NULL,                       -- 'yaml', 'json'
  deprecation_date TIMESTAMPTZ,
  is_active BOOLEAN NOT NULL DEFAULT true,

  -- Linked stories (cross-schema references)
  linked_story_ids UUID[] NOT NULL DEFAULT '{}',

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_contracts_tenant_blueprint ON tracker_design.technical_contracts (tenant_id, blueprint_id);
CREATE INDEX idx_contracts_active ON tracker_design.technical_contracts (tenant_id, is_active) WHERE is_active = true;

4.3 adrs

CREATE TABLE tracker_design.adrs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  blueprint_id UUID NOT NULL,

  -- Core fields
  title TEXT NOT NULL,
  context TEXT NOT NULL,
  decision TEXT NOT NULL,
  consequences TEXT NOT NULL,
  author_id UUID NOT NULL,

  -- Status
  status TEXT NOT NULL DEFAULT 'proposed'
    CHECK (status IN ('proposed', 'under_review', 'accepted', 'superseded', 'rejected')),
  superseded_by UUID,

  -- Tags
  tags TEXT[] NOT NULL DEFAULT '{}',

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_adrs_tenant_blueprint ON tracker_design.adrs (tenant_id, blueprint_id);
CREATE INDEX idx_adrs_tenant_status ON tracker_design.adrs (tenant_id, status);

4.4 data_schemas

CREATE TABLE tracker_design.data_schemas (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  blueprint_id UUID NOT NULL,

  -- Core fields
  schema_payload TEXT NOT NULL,               -- DDL, JSON Schema, or ERD description
  version TEXT NOT NULL DEFAULT '1.0.0',
  author_id UUID NOT NULL,

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_data_schemas_tenant_blueprint ON tracker_design.data_schemas (tenant_id, blueprint_id);

5. Schema: tracker_construction

5.1 implementation_cycles

CREATE SCHEMA IF NOT EXISTS tracker_construction;

CREATE TABLE tracker_construction.implementation_cycles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  blueprint_id UUID NOT NULL,                 -- Cross-schema: tracker_design.technical_blueprints.id

  -- Core fields
  name TEXT NOT NULL,
  sprint_goal TEXT,
  start_date TIMESTAMPTZ NOT NULL,
  end_date TIMESTAMPTZ NOT NULL,
  total_capacity_hours INTEGER,
  completed_points INTEGER DEFAULT 0,

  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('planned', 'active', 'frozen', 'closed')),

  -- Stories in this cycle
  technical_story_ids UUID[] NOT NULL DEFAULT '{}',

  -- Requirement Checklist
  requirement_checklist JSONB NOT NULL DEFAULT '{"items": []}',

  -- Approval gate
  construction_gate JSONB,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_cycles_tenant_blueprint ON tracker_construction.implementation_cycles (tenant_id, blueprint_id);
CREATE INDEX idx_cycles_tenant_status ON tracker_construction.implementation_cycles (tenant_id, status);
CREATE INDEX idx_cycles_date_range ON tracker_construction.implementation_cycles (tenant_id, start_date, end_date);

5.2 technical_stories

CREATE TABLE tracker_construction.technical_stories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  cycle_id UUID NOT NULL,

  -- Source reference (cross-schema: tracker_discovery.user_stories.id)
  parent_source_id UUID,

  -- Status
  status TEXT NOT NULL DEFAULT 'todo'
    CHECK (status IN ('todo', 'in_progress', 'in_review', 'drift_detected', 'done', 'blocked')),

  -- Peer review
  peer_review_id UUID,

  -- Assignment and estimation
  developer_id UUID,
  complexity INTEGER,
  estimated_hours INTEGER,
  logged_hours INTEGER DEFAULT 0,

  -- Labels
  labels TEXT[] NOT NULL DEFAULT '{}',

  -- Code branch (Value Object as JSONB)
  code_branch JSONB,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_tech_stories_tenant_cycle ON tracker_construction.technical_stories (tenant_id, cycle_id);
CREATE INDEX idx_tech_stories_developer ON tracker_construction.technical_stories (tenant_id, developer_id);
CREATE INDEX idx_tech_stories_status ON tracker_construction.technical_stories (tenant_id, status);

5.3 peer_reviews

CREATE TABLE tracker_construction.peer_reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  technical_story_id UUID NOT NULL,

  -- External reference (e.g., GitHub PR ID)
  external_id TEXT,

  -- Review status
  review_status TEXT NOT NULL DEFAULT 'pending'
    CHECK (review_status IN ('pending', 'in_progress', 'approved', 'changes_requested', 'merged')),

  -- PR details
  pull_request_url TEXT,
  source_branch TEXT,
  target_branch TEXT,
  ci_pipeline_status TEXT,
  merge_conflict BOOLEAN DEFAULT false,

  -- Reviewer decisions (JSONB array of ReviewerDecision VOs)
  reviewer_decisions JSONB NOT NULL DEFAULT '[]',

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_peer_reviews_tenant_story ON tracker_construction.peer_reviews (tenant_id, technical_story_id);
CREATE INDEX idx_peer_reviews_external ON tracker_construction.peer_reviews (tenant_id, external_id) WHERE external_id IS NOT NULL;

6. Schema: tracker_qa

6.1 test_cycles

CREATE SCHEMA IF NOT EXISTS tracker_qa;

CREATE TABLE tracker_qa.test_cycles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  implementation_cycle_id UUID NOT NULL,      -- Cross-schema: tracker_construction.implementation_cycles.id

  -- Core fields
  target_version TEXT NOT NULL,
  start_date TIMESTAMPTZ NOT NULL,
  end_date TIMESTAMPTZ,
  pass_rate_percentage FLOAT,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('planned', 'active', 'closed')),

  qa_lead_id UUID NOT NULL,

  -- Execution IDs
  execution_ids UUID[] NOT NULL DEFAULT '{}',

  -- Approval gate
  quality_gate JSONB,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_test_cycles_tenant_cycle ON tracker_qa.test_cycles (tenant_id, implementation_cycle_id);
CREATE INDEX idx_test_cycles_tenant_status ON tracker_qa.test_cycles (tenant_id, status);

6.2 test_executions

CREATE TABLE tracker_qa.test_executions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  cycle_id UUID NOT NULL,
  technical_story_id UUID NOT NULL,           -- Cross-schema: tracker_construction.technical_stories.id

  -- Core fields
  test_case_code TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'passed', 'failed', 'blocked', 'skipped')),
  executor_id UUID NOT NULL,
  execution_logs TEXT,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_test_executions_tenant_cycle ON tracker_qa.test_executions (tenant_id, cycle_id);
CREATE INDEX idx_test_executions_status ON tracker_qa.test_executions (tenant_id, status);

6.3 defects

CREATE TABLE tracker_qa.defects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  test_execution_id UUID,                      -- Cross-schema: tracker_qa.test_executions.id
  linked_story_id UUID,                        -- Cross-schema: tracker_construction.technical_stories.id

  -- Core fields
  severity TEXT NOT NULL
    CHECK (severity IN ('critical', 'major', 'minor', 'cosmetic')),
  priority TEXT NOT NULL
    CHECK (priority IN ('p0', 'p1', 'p2', 'p3')),
  status TEXT NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'in_progress', 'resolved', 'closed', 'duplicate')),
  reporter_id UUID NOT NULL,
  assignee_id UUID,

  -- Description
  description TEXT NOT NULL,

  -- Labels
  labels TEXT[] NOT NULL DEFAULT '{}',

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_defects_tenant_status ON tracker_qa.defects (tenant_id, status);
CREATE INDEX idx_defects_assignee ON tracker_qa.defects (tenant_id, assignee_id);
CREATE INDEX idx_defects_severity ON tracker_qa.defects (tenant_id, severity);

7. Schema: tracker_release

7.1 release_packages

CREATE SCHEMA IF NOT EXISTS tracker_release;

CREATE TABLE tracker_release.release_packages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  version_number TEXT NOT NULL,
  release_notes TEXT,
  status TEXT NOT NULL DEFAULT 'planned'
    CHECK (status IN ('planned', 'approved', 'deployed', 'rolled_back', 'cancelled')),
  release_manager_id UUID NOT NULL,

  -- Validated story IDs (cross-schema references)
  validated_story_ids UUID[] NOT NULL DEFAULT '{}',

  -- Release gate
  release_gate JSONB,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_release_packages_tenant_status ON tracker_release.release_packages (tenant_id, status);
CREATE INDEX idx_release_packages_version ON tracker_release.release_packages (tenant_id, version_number);

7.2 deployment_records

CREATE TABLE tracker_release.deployment_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  release_package_id UUID NOT NULL,            -- Cross-schema: tracker_release.release_packages.id
  environment_id UUID NOT NULL,                -- References tracker_release.environments.id (not cross-schema FK)

  -- Core fields
  deployed_by_id UUID NOT NULL,
  deployment_date TIMESTAMPTZ NOT NULL DEFAULT now(),
  status TEXT NOT NULL DEFAULT 'in_progress'
    CHECK (status IN ('in_progress', 'success', 'failed', 'rolled_back')),
  rollback_url TEXT,

  -- External references
  external_refs JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_deployment_records_tenant_package ON tracker_release.deployment_records (tenant_id, release_package_id);
CREATE INDEX idx_deployment_records_environment ON tracker_release.deployment_records (tenant_id, environment_id);
CREATE INDEX idx_deployment_records_status ON tracker_release.deployment_records (tenant_id, status);

7.3 environments

CREATE TABLE tracker_release.environments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  name TEXT NOT NULL,
  type TEXT NOT NULL
    CHECK (type IN ('development', 'staging', 'uat', 'production')),
  configuration JSONB NOT NULL DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_environments_tenant_type ON tracker_release.environments (tenant_id, type);

8. Schema: tracker_governance

8.1 satellite_products

CREATE SCHEMA IF NOT EXISTS tracker_governance;

CREATE TABLE tracker_governance.satellite_products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  name TEXT NOT NULL,
  description TEXT,
  upstream_core_version TEXT NOT NULL,

  -- BMAD agent modes per phase (JSONB)
  agent_modes JSONB NOT NULL DEFAULT '{}',

  -- Compliance status
  compliance_status TEXT NOT NULL DEFAULT 'pending'
    CHECK (compliance_status IN ('pending', 'compliant', 'non_compliant', 'unknown')),

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_satellite_products_tenant ON tracker_governance.satellite_products (tenant_id);

8.2 sdlc_executions

CREATE TABLE tracker_governance.sdlc_executions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  satellite_product_id UUID NOT NULL,
  initiative_id UUID NOT NULL,                 -- Cross-schema: tracker_discovery.initiatives.id

  -- Current gate state
  current_phase TEXT NOT NULL
    CHECK (current_phase IN ('discovery', 'design', 'construction', 'qa', 'release')),
  phase_status TEXT NOT NULL
    CHECK (phase_status IN ('pending', 'in_progress', 'blocked', 'completed', 'failed')),
  blockers JSONB NOT NULL DEFAULT '[]',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_sdlc_executions_tenant_initiative ON tracker_governance.sdlc_executions (tenant_id, initiative_id);
CREATE INDEX idx_sdlc_executions_tenant_phase ON tracker_governance.sdlc_executions (tenant_id, current_phase, phase_status);

8.3 phase_gate_states

CREATE TABLE tracker_governance.phase_gate_states (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  sdlc_execution_id UUID NOT NULL,

  -- Phase gate details
  phase TEXT NOT NULL
    CHECK (phase IN ('discovery', 'design', 'construction', 'qa', 'release')),
  gate_status TEXT NOT NULL
    CHECK (gate_status IN ('pending', 'pass', 'fail', 'blocked')),
  evaluated_at TIMESTAMPTZ,
  evaluated_by UUID,

  -- Gate evaluation details
  evaluation_details JSONB NOT NULL DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_phase_gate_states_tenant_sdlc ON tracker_governance.phase_gate_states (tenant_id, sdlc_execution_id);

8.4 exception_requests

CREATE TABLE tracker_governance.exception_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Reference
  initiative_id UUID NOT NULL,
  phase TEXT NOT NULL,

  -- Exception details
  exception_type TEXT NOT NULL,
  justification TEXT NOT NULL,
  requested_by UUID NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'approved', 'rejected', 'superseded')),

  -- Decision
  decided_by UUID,
  decided_at TIMESTAMPTZ,
  decision_notes TEXT,

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_exception_requests_tenant_status ON tracker_governance.exception_requests (tenant_id, status);

9. Schema: tracker_artifacts

9.1 artifact_definitions

CREATE SCHEMA IF NOT EXISTS tracker_artifacts;

CREATE TABLE tracker_artifacts.artifact_definitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  name TEXT NOT NULL,
  description TEXT,
  artifact_type TEXT NOT NULL,                -- e.g., 'test-summary-report', 'release-notes', 'drift-report'
  version TEXT NOT NULL DEFAULT '1.0.0',

  -- Schema reference (URL from Evolith Core)
  schema_url TEXT,

  -- Phase applicability
  applicable_phases TEXT[] NOT NULL DEFAULT '{}',

  -- Required for gate
  required_for_gates TEXT[] NOT NULL DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_artifact_definitions_tenant_type ON tracker_artifacts.artifact_definitions (tenant_id, artifact_type);

9.2 artifact_instances

CREATE TABLE tracker_artifacts.artifact_instances (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  definition_id UUID NOT NULL,                -- References tracker_artifacts.artifact_definitions.id

  -- Reference to producing aggregate
  source_aggregate_type TEXT NOT NULL,        -- e.g., 'TestCycle', 'ReleasePackage'
  source_aggregate_id UUID NOT NULL,

  -- Phase
  phase TEXT NOT NULL,

  -- Content
  content JSONB NOT NULL DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_artifact_instances_tenant_definition ON tracker_artifacts.artifact_instances (tenant_id, definition_id);
CREATE INDEX idx_artifact_instances_source ON tracker_artifacts.artifact_instances (tenant_id, source_aggregate_type, source_aggregate_id);

9.3 evidence_records

CREATE TABLE tracker_artifacts.evidence_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  artifact_instance_id UUID NOT NULL,

  -- Evidence details
  evidence_type TEXT NOT NULL,
  evidence_content JSONB NOT NULL DEFAULT '{}',

  -- Compliance
  is_compliant BOOLEAN,
  compliance_notes TEXT,

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_evidence_records_tenant_artifact ON tracker_artifacts.evidence_records (tenant_id, artifact_instance_id);

10. Schema: tracker_metrics

10.1 scorecard_definitions

CREATE SCHEMA IF NOT EXISTS tracker_metrics;

CREATE TABLE tracker_metrics.scorecard_definitions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  name TEXT NOT NULL,                         -- e.g., 'DORA Scorecard', 'SPACE Scorecard'
  description TEXT,
  metric_types TEXT[] NOT NULL DEFAULT '{}',  -- e.g., ['deployment_frequency', 'lead_time']

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_scorecard_definitions_tenant ON tracker_metrics.scorecard_definitions (tenant_id);

10.2 metric_snapshots

CREATE TABLE tracker_metrics.metric_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  scorecard_definition_id UUID NOT NULL,
  initiative_id UUID NOT NULL,                -- Cross-schema: tracker_discovery.initiatives.id

  -- Snapshot values (JSONB — flexible metric storage)
  metrics JSONB NOT NULL DEFAULT '{}',

  -- Period
  period_start TIMESTAMPTZ NOT NULL,
  period_end TIMESTAMPTZ NOT NULL,
  computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_metric_snapshots_tenant_initiative ON tracker_metrics.metric_snapshots (tenant_id, initiative_id);
CREATE INDEX idx_metric_snapshots_period ON tracker_metrics.metric_snapshots (tenant_id, period_start, period_end);

10.3 drift_alerts

CREATE TABLE tracker_metrics.drift_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  initiative_id UUID NOT NULL,

  -- Drift details
  drift_type TEXT NOT NULL,                   -- e.g., 'architecture_drift', 'scope_drift'
  severity TEXT NOT NULL
    CHECK (severity IN ('low', 'medium', 'high', 'critical')),
  description TEXT NOT NULL,
  detected_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Resolution
  resolved_at TIMESTAMPTZ,
  resolved_by UUID,

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_drift_alerts_tenant_initiative ON tracker_metrics.drift_alerts (tenant_id, initiative_id);
CREATE INDEX idx_drift_alerts_unresolved ON tracker_metrics.drift_alerts (tenant_id) WHERE resolved_at IS NULL;

11. Schema: tracker_integration

11.1 integration_endpoints

CREATE SCHEMA IF NOT EXISTS tracker_integration;

CREATE TABLE tracker_integration.integration_endpoints (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Core fields
  name TEXT NOT NULL,                         -- e.g., 'GitHub ACL', 'Jira ACL'
  system_type TEXT NOT NULL,                  -- e.g., 'github', 'jira', 'gitlab', 'harness'
  base_url TEXT,
  auth_config JSONB NOT NULL DEFAULT '{}',   -- Encrypted credentials (never logged)

  -- Status
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'error')),
  last_sync_at TIMESTAMPTZ,

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_integration_endpoints_tenant_system ON tracker_integration.integration_endpoints (tenant_id, system_type);

11.2 sync_records

CREATE TABLE tracker_integration.sync_records (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  integration_endpoint_id UUID NOT NULL,

  -- Sync details
  sync_type TEXT NOT NULL,                    -- e.g., 'webhook', 'polling', 'manual'
  direction TEXT NOT NULL
    CHECK (direction IN ('ingress', 'egress')),
  status TEXT NOT NULL
    CHECK (status IN ('success', 'failed', 'partial')),
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  items_processed INTEGER DEFAULT 0,
  items_failed INTEGER DEFAULT 0,
  error_message TEXT,

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_sync_records_tenant_endpoint ON tracker_integration.sync_records (tenant_id, integration_endpoint_id);
CREATE INDEX idx_sync_records_status ON tracker_integration.sync_records (tenant_id, status) WHERE status != 'success';

11.3 acl_adapters

CREATE TABLE tracker_integration.acl_adapters (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  integration_endpoint_id UUID NOT NULL,

  -- Adapter details
  adapter_type TEXT NOT NULL,                -- e.g., 'github_pr_mapper', 'jira_issue_mapper'
  adapter_config JSONB NOT NULL DEFAULT '{}',

  -- Audit
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by UUID NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by UUID NOT NULL,
  deleted_at TIMESTAMPTZ,
  version INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_acl_adapters_tenant_endpoint ON tracker_integration.acl_adapters (tenant_id, integration_endpoint_id);

12. Schema: tracker_audit (Append-Only)

12.1 audit_entries

CREATE SCHEMA IF NOT EXISTS tracker_audit;

CREATE TABLE tracker_audit.audit_entries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Context
  tenant_id UUID NOT NULL,
  user_id UUID NOT NULL,
  correlation_id UUID NOT NULL,

  -- Action details
  action TEXT NOT NULL,                       -- e.g., 'initiative.created', 'contract.approved'
  aggregate_type TEXT NOT NULL,               -- e.g., 'Initiative', 'TechnicalContract'
  aggregate_id UUID NOT NULL,

  -- Payload (changed fields, before/after)
  payload JSONB NOT NULL DEFAULT '{}',

  -- Metadata
  ip_address INET,
  user_agent TEXT,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Append-only: no UPDATE or DELETE allowed (enforced by Revoke in migration)
CREATE INDEX idx_audit_entries_tenant_aggregate ON tracker_audit.audit_entries (tenant_id, aggregate_type, aggregate_id);
CREATE INDEX idx_audit_entries_tenant_user ON tracker_audit.audit_entries (tenant_id, user_id);
CREATE INDEX idx_audit_entries_occurred_at ON tracker_audit.audit_entries (tenant_id, occurred_at DESC);
CREATE INDEX idx_audit_entries_correlation ON tracker_audit.audit_entries (correlation_id);

12.2 Append-Only Enforcement

-- Migration to enforce append-only on audit_entries
REVOKE UPDATE, DELETE ON tracker_audit.audit_entries FROM app_user;

-- Alternative: use triggers for database-level enforcement
CREATE OR REPLACE FUNCTION prevent_audit_modification()
RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Audit entries cannot be modified or deleted';
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_entries_immutable
BEFORE UPDATE OR DELETE ON tracker_audit.audit_entries
FOR EACH ROW EXECUTE FUNCTION prevent_audit_modification();

13. Transactional Outbox

13.1 outbox_messages

-- Shared schema for cross-context event outbox
-- Lives in tracker_governance schema (or dedicated tracker_outbox schema)
CREATE TABLE tracker_governance.outbox_messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,

  -- Event routing
  event_type TEXT NOT NULL,
  aggregate_id UUID NOT NULL,
  aggregate_type TEXT NOT NULL,

  -- Payload
  payload JSONB NOT NULL,

  -- Processing state
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ,
  retry_count INTEGER NOT NULL DEFAULT 0,
  last_error TEXT
);

CREATE INDEX idx_outbox_pending ON tracker_governance.outbox_messages (tenant_id) WHERE processed_at IS NULL;
CREATE INDEX idx_outbox_created ON tracker_governance.outbox_messages (tenant_id, created_at ASC) WHERE processed_at IS NULL;

14. Row-Level Security (Multi-Tenancy)

14.1 RLS Policy Definition

-- Enable RLS on all tenant-scoped tables
ALTER TABLE tracker_discovery.initiatives ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_design.technical_blueprints ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_construction.implementation_cycles ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_qa.test_cycles ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_release.release_packages ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_governance.satellite_products ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_artifacts.artifact_definitions ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_metrics.scorecard_definitions ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_integration.integration_endpoints ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracker_audit.audit_entries ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policy (applied to ALL operations)
CREATE POLICY tenant_isolation ON tracker_discovery.initiatives
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Repeat for each schema's tables
CREATE POLICY tenant_isolation ON tracker_design.technical_blueprints
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- ... (same pattern for all tenant-scoped tables)

14.2 Setting Tenant Context

-- Set tenant context per session/transaction
SET app.current_tenant_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

-- In application code, set before each transaction
-- In TypeORM: queryRunner.connection.driver.connection.query('SET app.current_tenant_id = $1', [tenantId])

15. Migration Strategy

15.1 Migration Tool

Use TypeORM Migrations (typeorm migration:* commands) or db-migrate with SQL migration scripts.

15.2 Migration Naming Convention

src/migrations/
├── 0001-initialize-discovery-schema.sql
├── 0002-initialize-design-schema.sql
├── 0003-initialize-construction-schema.sql
├── 0004-initialize-qa-schema.sql
├── 0005-initialize-release-schema.sql
├── 0006-initialize-governance-schema.sql
├── 0007-initialize-artifacts-schema.sql
├── 0008-initialize-metrics-schema.sql
├── 0009-initialize-integration-schema.sql
├── 0010-initialize-audit-schema.sql
├── 0011-add-rls-policies.sql
└── 0012-initialize-outbox.sql

15.3 Migration Locking

-- Use advisory locks for safe concurrent migrations
SELECT pg_advisory_lock(1);

DO $$
BEGIN
  -- migration logic here
END $$;

SELECT pg_advisory_unlock(1);

References

Tracker Target Architecture — Primary TAD
Discovery DDD Model
Design DDD Model
Construction DDD Model
QA DDD Model
Release DDD Model
ADR-0043: ORM Strategy

FilesExpand file tree

tracker-postgresql-data-design.md

Latest commit

History