Celes Renata
a72f336ad1
Multi-stage evidence-grounded inference architecture replacing the monolithic 9B model extraction pipeline. CPU-first specialist services handle routine extraction while the 9B vLLM model is preserved for semantic adjudication of ambiguous cases. Key components: - Capability-aware inference gateway (OpenAI-compatible + Ollama) - Endpoint registry with DB migrations and REST API - Sentence-aware document segmenter (property tests) - Deterministic financial parsing with offset integrity - Symbol resolution with ambiguity detection - Specialist service (GLiNER2, dynamic batching, K8s deployment) - Company-specific sentiment (FinBERT, calibration) - Retrieval-based novelty and duplicate detection - Confidence calibration pipeline - Deterministic routing engine (property tests) - 9B adjudication layer with VRAM gating - Stock-specific impact model (features, labels, baseline, trained) - Pipeline orchestrator (state machine, queues, leases, feature flags) - Bounded parallelism (async workers, semaphore, load shedding) - Observability (tracing, metrics, alerts) - Compatibility adapter (v3→v2 golden mapping tests) - Shadow/canary promotion framework - Active learning and fine-tuning pipeline Test results: 1,161 tests pass, ruff lint clean. All 282 spec tasks completed.
351 lines
18 KiB
PL/PgSQL
351 lines
18 KiB
PL/PgSQL
-- Migration 041: V3 Pipeline Persistence Tables
|
|
-- Creates tables for the Intelligence Pipeline v3 staged evidence architecture:
|
|
-- v3_pipeline_runs, v3_stage_runs, v3_document_chunks, v3_evidence_spans,
|
|
-- v3_extracted_entities, v3_extracted_facts, v3_extracted_relations,
|
|
-- v3_rejected_candidates, v3_company_signal_candidates,
|
|
-- v3_adjudication_decisions, v3_routing_decisions, v3_stage_lineage
|
|
-- Includes idempotency keys, immutable-revision constraints, and appropriate indexes.
|
|
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
-- 20.1: Pipeline runs and stage runs
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
-- ─── v3_pipeline_runs ─────────────────────────────────────────────────────────
|
|
-- Top-level pipeline execution record for a document.
|
|
CREATE TABLE IF NOT EXISTS v3_pipeline_runs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
document_id UUID NOT NULL,
|
|
pipeline_version TEXT NOT NULL DEFAULT 'v3.0',
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'completed', 'failed')),
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
idempotency_key TEXT NOT NULL UNIQUE,
|
|
error TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_pipeline_runs_document
|
|
ON v3_pipeline_runs(document_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_pipeline_runs_status
|
|
ON v3_pipeline_runs(status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_pipeline_runs_created
|
|
ON v3_pipeline_runs(created_at DESC);
|
|
|
|
-- ─── v3_stage_runs ────────────────────────────────────────────────────────────
|
|
-- Individual stage execution within a pipeline run.
|
|
CREATE TABLE IF NOT EXISTS v3_stage_runs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
stage TEXT NOT NULL CHECK (stage IN (
|
|
'segmentation', 'extraction', 'sentiment', 'novelty',
|
|
'routing', 'adjudication', 'impact', 'persistence'
|
|
)),
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'completed', 'failed', 'skipped')),
|
|
started_at TIMESTAMPTZ,
|
|
completed_at TIMESTAMPTZ,
|
|
endpoint_id UUID REFERENCES inference_endpoints(id) ON DELETE SET NULL,
|
|
deployment_id UUID REFERENCES model_deployments(id) ON DELETE SET NULL,
|
|
model_version TEXT,
|
|
schema_version TEXT,
|
|
input_refs JSONB NOT NULL DEFAULT '[]',
|
|
output_refs JSONB NOT NULL DEFAULT '[]',
|
|
trace_id TEXT,
|
|
error TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_stage_runs_pipeline
|
|
ON v3_stage_runs(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_stage_runs_stage
|
|
ON v3_stage_runs(stage);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_stage_runs_status
|
|
ON v3_stage_runs(status);
|
|
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
-- 20.2: Document chunks and evidence spans
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
-- ─── v3_document_chunks ───────────────────────────────────────────────────────
|
|
-- Segmented document chunks with offset tracking.
|
|
CREATE TABLE IF NOT EXISTS v3_document_chunks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
document_id UUID NOT NULL,
|
|
chunk_id TEXT NOT NULL,
|
|
section_path JSONB NOT NULL DEFAULT '[]',
|
|
speaker TEXT,
|
|
start_char INTEGER NOT NULL,
|
|
end_char INTEGER NOT NULL,
|
|
text TEXT NOT NULL,
|
|
overlap_left INTEGER NOT NULL DEFAULT 0,
|
|
overlap_right INTEGER NOT NULL DEFAULT 0,
|
|
boilerplate_score REAL NOT NULL DEFAULT 0.0,
|
|
document_type TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE(document_id, chunk_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_document_chunks_document
|
|
ON v3_document_chunks(document_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_document_chunks_type
|
|
ON v3_document_chunks(document_type)
|
|
WHERE document_type IS NOT NULL;
|
|
|
|
-- ─── v3_evidence_spans ────────────────────────────────────────────────────────
|
|
-- Exact source text with character offsets for provenance.
|
|
CREATE TABLE IF NOT EXISTS v3_evidence_spans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
document_id UUID NOT NULL,
|
|
chunk_id TEXT,
|
|
start_char INTEGER NOT NULL,
|
|
end_char INTEGER NOT NULL,
|
|
text TEXT NOT NULL,
|
|
checksum TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_evidence_spans_document
|
|
ON v3_evidence_spans(document_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_evidence_spans_checksum
|
|
ON v3_evidence_spans(checksum);
|
|
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
-- 20.3: Extracted entities, facts, relations, and rejected candidates
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
-- ─── v3_extracted_entities ────────────────────────────────────────────────────
|
|
-- Entities discovered during extraction (companies, people, orgs, etc.).
|
|
CREATE TABLE IF NOT EXISTS v3_extracted_entities (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
entity_type TEXT NOT NULL,
|
|
literal_text TEXT NOT NULL,
|
|
canonical_id UUID,
|
|
evidence_span_id UUID REFERENCES v3_evidence_spans(id) ON DELETE SET NULL,
|
|
confidence REAL NOT NULL DEFAULT 0.0,
|
|
derivation TEXT NOT NULL DEFAULT 'specialist',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_entities_pipeline
|
|
ON v3_extracted_entities(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_entities_canonical
|
|
ON v3_extracted_entities(canonical_id)
|
|
WHERE canonical_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_entities_type
|
|
ON v3_extracted_entities(entity_type);
|
|
|
|
-- ─── v3_extracted_facts ───────────────────────────────────────────────────────
|
|
-- Structured facts (numeric values, dates, amounts, etc.).
|
|
CREATE TABLE IF NOT EXISTS v3_extracted_facts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
fact_type TEXT NOT NULL,
|
|
subject_entity_id UUID REFERENCES v3_extracted_entities(id) ON DELETE SET NULL,
|
|
predicate TEXT NOT NULL,
|
|
literal_value TEXT NOT NULL,
|
|
normalized_value JSONB,
|
|
unit TEXT,
|
|
period JSONB,
|
|
evidence_span_ids UUID[] NOT NULL DEFAULT '{}',
|
|
confidence REAL NOT NULL DEFAULT 0.0,
|
|
derivation TEXT NOT NULL DEFAULT 'deterministic',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_facts_pipeline
|
|
ON v3_extracted_facts(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_facts_subject
|
|
ON v3_extracted_facts(subject_entity_id)
|
|
WHERE subject_entity_id IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_facts_type
|
|
ON v3_extracted_facts(fact_type);
|
|
|
|
-- ─── v3_extracted_relations ───────────────────────────────────────────────────
|
|
-- Relations between entities (competes_with, supplies, etc.).
|
|
CREATE TABLE IF NOT EXISTS v3_extracted_relations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
relation_type TEXT NOT NULL,
|
|
source_entity_id UUID NOT NULL REFERENCES v3_extracted_entities(id) ON DELETE CASCADE,
|
|
target_entity_id UUID NOT NULL REFERENCES v3_extracted_entities(id) ON DELETE CASCADE,
|
|
evidence_span_ids UUID[] NOT NULL DEFAULT '{}',
|
|
confidence REAL NOT NULL DEFAULT 0.0,
|
|
derivation TEXT NOT NULL DEFAULT 'specialist',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_relations_pipeline
|
|
ON v3_extracted_relations(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_relations_source
|
|
ON v3_extracted_relations(source_entity_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_extracted_relations_target
|
|
ON v3_extracted_relations(target_entity_id);
|
|
|
|
-- ─── v3_rejected_candidates ──────────────────────────────────────────────────
|
|
-- Candidates that failed validation or were rejected by a stage.
|
|
CREATE TABLE IF NOT EXISTS v3_rejected_candidates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
candidate_type TEXT NOT NULL,
|
|
candidate_data JSONB NOT NULL,
|
|
rejection_reason TEXT NOT NULL,
|
|
stage TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_rejected_candidates_pipeline
|
|
ON v3_rejected_candidates(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_rejected_candidates_stage
|
|
ON v3_rejected_candidates(stage);
|
|
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
-- 20.4: Company signal candidates and probability distributions
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
-- ─── v3_company_signal_candidates ─────────────────────────────────────────────
|
|
-- Per-company signal output with full probability distributions.
|
|
CREATE TABLE IF NOT EXISTS v3_company_signal_candidates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
|
|
relevance_probability REAL NOT NULL DEFAULT 0.0,
|
|
event_probabilities JSONB NOT NULL DEFAULT '{}',
|
|
sentiment_probabilities JSONB NOT NULL DEFAULT '{}',
|
|
direction_probabilities JSONB NOT NULL DEFAULT '{}',
|
|
horizon_probabilities JSONB NOT NULL DEFAULT '{}',
|
|
expected_magnitude REAL,
|
|
evidence_span_ids UUID[] NOT NULL DEFAULT '{}',
|
|
routing_reasons TEXT[] NOT NULL DEFAULT '{}',
|
|
adjudicated BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_signal_candidates_pipeline
|
|
ON v3_company_signal_candidates(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_signal_candidates_company
|
|
ON v3_company_signal_candidates(company_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_signal_candidates_adjudicated
|
|
ON v3_company_signal_candidates(adjudicated)
|
|
WHERE adjudicated = TRUE;
|
|
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
-- 20.5: Adjudication decisions, routing reasons, calibration, and model lineage
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
-- ─── v3_stage_lineage ─────────────────────────────────────────────────────────
|
|
-- Detailed model/endpoint lineage for each stage invocation.
|
|
-- Created before adjudication_decisions because it is referenced as a FK.
|
|
CREATE TABLE IF NOT EXISTS v3_stage_lineage (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
stage_run_id UUID NOT NULL REFERENCES v3_stage_runs(id) ON DELETE CASCADE,
|
|
endpoint_id UUID REFERENCES inference_endpoints(id) ON DELETE SET NULL,
|
|
deployment_id UUID REFERENCES model_deployments(id) ON DELETE SET NULL,
|
|
model TEXT,
|
|
protocol TEXT,
|
|
structured_mode TEXT,
|
|
request_id TEXT,
|
|
latency_ms INTEGER,
|
|
retries INTEGER NOT NULL DEFAULT 0,
|
|
trace_id TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_stage_lineage_stage_run
|
|
ON v3_stage_lineage(stage_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_stage_lineage_endpoint
|
|
ON v3_stage_lineage(endpoint_id)
|
|
WHERE endpoint_id IS NOT NULL;
|
|
|
|
-- ─── v3_adjudication_decisions ────────────────────────────────────────────────
|
|
-- Decisions made by the 9B adjudicator for ambiguous documents.
|
|
CREATE TABLE IF NOT EXISTS v3_adjudication_decisions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
question_codes TEXT[] NOT NULL DEFAULT '{}',
|
|
candidates JSONB NOT NULL DEFAULT '{}',
|
|
decision JSONB NOT NULL DEFAULT '{}',
|
|
evidence_span_ids UUID[] NOT NULL DEFAULT '{}',
|
|
model_lineage_id UUID REFERENCES v3_stage_lineage(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_adjudication_pipeline
|
|
ON v3_adjudication_decisions(pipeline_run_id);
|
|
|
|
-- ─── v3_routing_decisions ─────────────────────────────────────────────────────
|
|
-- Records of fast-path vs adjudication routing decisions.
|
|
CREATE TABLE IF NOT EXISTS v3_routing_decisions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
pipeline_run_id UUID NOT NULL REFERENCES v3_pipeline_runs(id) ON DELETE CASCADE,
|
|
document_id UUID NOT NULL,
|
|
route TEXT NOT NULL CHECK (route IN ('fast_path', 'adjudication')),
|
|
reason_codes TEXT[] NOT NULL DEFAULT '{}',
|
|
confidence_features JSONB NOT NULL DEFAULT '{}',
|
|
decided_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_routing_pipeline
|
|
ON v3_routing_decisions(pipeline_run_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_v3_routing_route
|
|
ON v3_routing_decisions(route);
|
|
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
-- 20.6: Idempotency and immutable-revision constraints
|
|
-- ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
-- Pipeline runs: idempotency_key UNIQUE is already defined above in the table.
|
|
-- Stage runs: unique per pipeline_run_id + stage to prevent duplicate stage execution.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_v3_stage_runs_idempotent
|
|
ON v3_stage_runs(pipeline_run_id, stage);
|
|
|
|
-- Company signal candidates: unique per pipeline_run_id + company_id.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_v3_signal_candidates_idempotent
|
|
ON v3_company_signal_candidates(pipeline_run_id, company_id);
|
|
|
|
-- Routing decisions: unique per pipeline_run_id (one routing decision per run).
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_v3_routing_idempotent
|
|
ON v3_routing_decisions(pipeline_run_id);
|
|
|
|
-- Evidence spans: unique by document + checksum to avoid storing duplicates.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_v3_evidence_spans_idempotent
|
|
ON v3_evidence_spans(document_id, checksum);
|
|
|
|
-- Immutable revision rule: pipeline_runs and stage_runs cannot be updated once completed.
|
|
-- Enforced via trigger: reject updates to rows where status = 'completed' or 'failed'.
|
|
CREATE OR REPLACE FUNCTION v3_immutable_completed_row()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF OLD.status IN ('completed', 'failed') THEN
|
|
RAISE EXCEPTION 'Cannot modify a % record with status=%', TG_TABLE_NAME, OLD.status;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trg_v3_pipeline_runs_immutable ON v3_pipeline_runs;
|
|
CREATE TRIGGER trg_v3_pipeline_runs_immutable
|
|
BEFORE UPDATE ON v3_pipeline_runs
|
|
FOR EACH ROW EXECUTE FUNCTION v3_immutable_completed_row();
|
|
|
|
DROP TRIGGER IF EXISTS trg_v3_stage_runs_immutable ON v3_stage_runs;
|
|
CREATE TRIGGER trg_v3_stage_runs_immutable
|
|
BEFORE UPDATE ON v3_stage_runs
|
|
FOR EACH ROW EXECUTE FUNCTION v3_immutable_completed_row();
|