Skip to main content

Database Schema

Page Outline

Database Schema​

The database schema is designed to support efficient querying of historical decisions while maintaining flexibility for different decision types and scenarios. JSONB columns are used for flexible schema evolution without migrations.

Decisions Table​

The decisions table stores all decisions made by DIA, providing a complete audit trail and enabling historical analysis.

CREATE TABLE decisions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id TEXT NOT NULL,
  type TEXT NOT NULL,  -- 'simulation', 'analysis', 'routing', 'policy'
  name TEXT NOT NULL,  -- Human-readable decision name
  parameters JSONB,    -- Decision-specific parameters (flexible schema)
  inputs JSONB,        -- Input context (models, datasets, etc.)
  outputs JSONB,       -- Decision outputs and results
  impact_score NUMERIC, -- Calculated impact score (0.0 to 1.0)
  governance_token TEXT, -- Token from GA validation
  reasoning TEXT,      -- Human-readable reasoning for the decision
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for efficient querying
CREATE INDEX idx_decisions_tenant_id ON decisions(tenant_id);
CREATE INDEX idx_decisions_type ON decisions(type);
CREATE INDEX idx_decisions_created_at ON decisions(created_at DESC);
CREATE INDEX idx_decisions_governance_token ON decisions(governance_token);
CREATE INDEX idx_decisions_impact_score ON decisions(impact_score DESC);

-- GIN index for JSONB queries
CREATE INDEX idx_decisions_parameters_gin ON decisions USING GIN(parameters);
CREATE INDEX idx_decisions_outputs_gin ON decisions USING GIN(outputs);

Schema Design Rationale:

  • UUID Primary Key: Enables distributed ID generation without coordination
  • Tenant Isolation: tenant_id ensures multi-tenant data separation
  • Flexible Schema: JSONB columns allow evolution without migrations
  • Impact Score: Enables ranking and filtering of decisions by importance
  • Governance Token: Links decisions to specific policy validations
  • GIN Indexes: Enable efficient querying of JSONB fields

Simulations Table​

The simulations table stores detailed simulation results linked to decisions, enabling replay and analysis of what-if scenarios.

CREATE TABLE simulations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  decision_id UUID REFERENCES decisions(id) ON DELETE CASCADE,
  scenario JSONB NOT NULL,  -- Complete scenario definition
  result JSONB,            -- Simulation results
  status TEXT NOT NULL,    -- 'queued', 'running', 'completed', 'failed'
  run_time_sec INT,        -- Actual execution time
  error_message TEXT,      -- Error details if failed
  created_at TIMESTAMP DEFAULT NOW(),
  started_at TIMESTAMP,
  completed_at TIMESTAMP
);

-- Indexes
CREATE INDEX idx_simulations_decision_id ON simulations(decision_id);
CREATE INDEX idx_simulations_status ON simulations(status);
CREATE INDEX idx_simulations_created_at ON simulations(created_at DESC);

Relationship Design:

  • Foreign Key Constraint: Ensures referential integrity with CASCADE delete
  • Status Tracking: Enables monitoring of simulation job progress
  • Timing Fields: Support performance analysis and SLA monitoring

Additional Tables​

Analysis Results Table:

CREATE TABLE analysis_results (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  decision_id UUID REFERENCES decisions(id) ON DELETE CASCADE,
  query TEXT NOT NULL,
  summary TEXT,
  findings JSONB,
  recommendations JSONB,
  confidence NUMERIC,
  created_at TIMESTAMP DEFAULT NOW()
);

Vector Embeddings Table (for Chroma/Pinecone):

While embeddings are stored in Chroma/Pinecone, a reference table in Postgres maintains the mapping:

CREATE TABLE decision_embeddings (
  decision_id UUID PRIMARY KEY REFERENCES decisions(id) ON DELETE CASCADE,
  embedding_id TEXT NOT NULL,  -- ID in vector store
  embedding_model TEXT,         -- Model used for embedding
  created_at TIMESTAMP DEFAULT NOW()
);