← Back to Platform Architecture

Data Models — Overview

The Stratum Platform database contains 13 tables organized into three logical domains: Core Precedent Infrastructure, Marketplace & Credit System, and Appeal Workflow. This document shows how they relate and guides you to detailed documentation for each table.

Database at a Glance

Total: 13 tables. Connection: Fly Postgres 16. Row count estimate: 0 (seed data), ~10K precedents (production projection).

Entity Relationship Diagram

┌─────────────────────────────────────────────────────────────────┐ │ TENANCY & AUTHENTICATION LAYER │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ tenants (id, name, is_active) │ │ ↓ 1:N │ │ users (id, tenant_id, email, name, role) │ │ │ └─────────────────────────────────────────────────────────────────┘ ↓ (every table has tenant_id) ┌─────────────────────────────────────────────────────────────────┐ │ CORE PRECEDENT INFRASTRUCTURE │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ precedent_objects │ │ (id, payer, denial_type, loc, cluster_id, status, ...) │ │ ├─ 1:N → precedent_version_history (full state snapshots) │ │ ├─ 1:N → precedent_audit_log (compliance events) │ │ └─ N:M → appeals (precedent referenced in appeal evidence) │ │ │ │ payer_plans (id, payer_name, plan_year, state, coverage_data) │ │ └─ reference for precedent payer/coverage lookup │ │ │ └─────────────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────────────┐ │ MARKETPLACE & CREDIT SYSTEM │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ marketplace_clusters │ │ (cluster_id, payer, denial_type, loc, maturity_tier, ...) │ │ ├─ derived from precedent_objects (cluster_id grouping) │ │ ├─ 1:N → contribution_scores (per tenant, per cluster) │ │ └─ 1:N → marketplace_queries (audit log of cluster access) │ │ │ │ credit_ledger (event-sourced, immutable) │ │ (id, tenant_id, event_type, amount, reference_id, receipt_hash) │ │ ├─ contribution_mint (when precedent created) │ │ ├─ query_burn (when cluster accessed) │ │ ├─ membership_allocation (monthly baseline) │ │ └─ bonus_mint (high-quality contributions) │ │ │ │ contribution_scores (tenant_id, cluster_id, gap_score, total) │ │ └─ computed from credit_ledger and precedent outcomes │ │ │ │ marketplace_queries (tenant_id, cluster_id, credits_spent) │ │ └─ audit log of who queried what and when │ │ │ └─────────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────────┐ │ APPEAL WORKFLOW │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ appeals (id, tenant_id, denial_id, narrative, status, ...) │ │ ├─ created_by: user (who is building the appeal) │ │ ├─ selected_precedent_ids: UUID[] (evidence from precedents) │ │ ├─ 1:N → appeal_versions (draft history, auto-save snapshots) │ │ └─ 1:N → appeals_audit_log (submission events) │ │ │ │ appeal_versions (appeal_id, version_number, snapshot) │ │ └─ immutable full-state snapshots (like precedent_versions) │ │ │ │ appeals_audit_log (appeal_id, event_type, user_id, timestamp) │ │ └─ events: created, saved_draft, validation_run, submitted │ │ │ └─────────────────────────────────────────────────────────────────┘

Key Relationships

1. Multi-Tenancy (Every Table)

Every table has tenant_id except tenants and tenants itself. The tenant middleware in Express ensures all queries filter by tenant automatically. Pattern: All queries include WHERE tenant_id = $1

2. Precedent Versioning (Immutable History)

When a precedent is created or updated:

Current state is stored in precedent_objects

Full snapshot is written to precedent_version_history (immutable)

Events logged to precedent_audit_log

Why: Compliance audit trail. Investigators can see exactly what changed when.

3. Marketplace (Clustering + Contribution)

Precedents are grouped by cluster_id (format: "Payer

Domain	Tables	Purpose
Tenancy & Auth	`tenants`, `users`	Multi-tenant isolation, user roles
Core Precedents	`precedent_objects`, `precedent_version_history`, `precedent_audit_log`, `payer_plans`	Clinical denial precedents, versioning, compliance logging
Marketplace & Credit	`marketplace_clusters`, `credit_ledger`, `contribution_scores`, `marketplace_queries`	Network effect clustering, event-sourced credit system
Appeals Workflow	`appeals`, `appeal_versions`, `appeals_audit_log`	Appeal submissions, versioning, audit trail
Denial Type

LOC"):

All precedents with same cluster_id → one marketplace_clusters row

Per tenant, per cluster: one contribution_scores row (computes tenant's contribution to that cluster)

Every marketplace query → one marketplace_queries row (audit + credit tracking)

Why: Network effect gates. Only clusters with 2+ distinct tenants appear in marketplace.

4. Credit Ledger (Event-Sourced, Immutable)

Every credit transaction is an immutable ledger entry:

Contribution: +X credits when precedent created
Query: -Y credits when cluster accessed
Allocation: +Z credits monthly per tenant
Bonus: +W credits for high-quality contributions

Why:

5. Appeals (Workflow + Versioning)

Appeal drafted in appeals table
Each save → snapshot to appeal_versions (auto-save history)
Events logged to appeals_audit_log (created, saved, validated, submitted)

Why:

Table Index Strategy

By Query Pattern

Fast lookup tables (B-tree indexes):

idx_precedent_tenant — Most queries filter by tenant first
idx_precedent_cluster — Marketplace grouping
idx_precedent_payer, idx_precedent_denial_type, idx_precedent_loc — Search filters
idx_appeals_tenant, idx_appeals_status — Appeal listing
idx_cl_tenant — Credit balance queries (tenant + date)

Range queries (DESC indexes):

idx_precedent_last_validated DESC — Find outdated precedents
idx_appeals_created_at DESC — List recent appeals
idx_cl_created_at DESC (in index definition) — Ledger timeline
idx_audit_precedent (precedent_id, timestamp DESC) — Compliance audit trail

JSONB queries (GIN indexes):

idx_evidence_kit_gin — Search within evidence_kit JSONB
idx_traceability_gin — Search within traceability_ledger JSONB
idx_coverage_data_gin — Search within payer_plans coverage_data JSONB

Performance Expectations

Tenant isolation (filter by tenant_id): <5ms for 10K rows
Cluster lookup (filter by cluster_id): <5ms with index
Marketplace list (filter by maturity_tier + payer): <10ms with composite indexes
Credit balance (sum credit_ledger by tenant): ~50ms without aggregation (use ledger entries, don't compute)
Audit trail (list events by precedent_id): <10ms with reverse timestamp index

Constraints & Invariants

Data Integrity Constraints

-- Role validation
CONSTRAINT valid_role CHECK (role IN ('admin', 'biller', 'viewer'))

-- Precedent state machine
CONSTRAINT valid_status CHECK (status IN (
  'New', 'Evidence Gathering', 'Under Review', 'Complete', 'Archived'
))

-- Outcome tracking
CONSTRAINT valid_outcome CHECK (outcome IN (
  'Won', 'Lost', 'Partial', 'Pending', 'Abandoned'
))

-- SUD compliance (42 CFR Part 2)
CONSTRAINT sud_sharing_enforced CHECK (
  data_origin_type != 'SUD' OR sharing_restrictions != 'none'
)
-- Every SUD precedent MUST have consent_required or originator_only sharing

-- Appeal validation
CONSTRAINT valid_appeal_status CHECK (status IN (
  'Draft', 'Submitted', 'Under Review', 'Approved', 'Denied', 'Withdrawn', 'Archived'
))

Critical Invariants

Every row belongs to exactly one tenant — No cross-tenant queries without explicit filter
Precedent updates are immutable — Updates snapshot full state, never overwrite
Credit ledger is append-only — Never update or delete ledger entries, only INSERT
SUD data has special sharing rules — Enforcement at DB constraint level
Marketplace gate — Cluster only appears if 2+ distinct tenants contributed

Code reference

src/db/schema.sql — All table definitions (tenants through payer_plans)
src/db/marketplace-schema.sql — Marketplace system tables
src/db/migrations/002_appeals_infrastructure.sql — Appeals workflow tables
src/types/index.ts — TypeScript interfaces for all models
src/models/ — Database query helpers per domain