Repository navigation
PostgreSQL Schemas
Ayman Sayed edited this page Apr 15, 2026
·
1 revision
All complaint data is stored in a single PostgreSQL instance that also hosts pgvector embeddings. The schema is defined in app/db/models.py.
| Table | Purpose |
|---|---|
user_accounts |
Registered user credentials and roles |
complaint_cases |
Core complaint record — the central table |
classifications |
AI classification output per case |
risk_assessments |
AI risk scoring output per case |
resolutions |
AI resolution recommendation per case |
intake_sessions |
Live chat session state during intake |
workflow_runs |
One record per LangGraph agent execution |
workflow_steps |
One record per node within a workflow run |
complaint_embeddings |
Historical CFPB complaint vectors (RAG) |
resolution_embeddings |
Historical resolution outcome vectors (RAG) |
Stores registered user accounts.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
String(32) | No | UUID hex, primary key |
email |
String(254) | No | Unique, indexed |
password |
String(255) | No | Hashed password |
role |
String(20) | No |
user or admin
|
company |
String(200) | Yes | Organisation name |
user_id |
String(64) | No | Unique user identifier |
created_at |
DateTime | — | Auto-set on insert |
The central table. Every complaint submitted through the system has exactly one row here.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
String(32) | No | UUID hex, primary key |
status |
String(30) | No |
received, in_review, resolved, closed
|
consumer_narrative |
Text | No | Raw complaint text from the user |
product |
String(120) | Yes | e.g. "Credit card" |
sub_product |
String(120) | Yes | e.g. "General-purpose credit card" |
company |
String(200) | Yes | Company being complained about |
user_id |
String(64) | Yes | FK reference to the submitting user (indexed) |
state |
String(2) | Yes | US state code |
zip_code |
String(5) | Yes | |
channel |
String(20) | Yes |
web, email, voice
|
submitted_at |
DateTime | — | When the user submitted |
created_at |
DateTime | — | Record creation time |
updated_at |
DateTime | — | Auto-updated on every write |
severity_class |
String(40) | Yes |
critical, high, medium, low
|
team_assignment |
String(120) | Yes | Team that owns this case |
sla_class |
String(40) | Yes | SLA tier assigned by the supervisor agent |
routed_to |
String(120) | Yes | Destination agent or team |
review_notes |
Text | Yes | Free-text notes added by human reviewers |
external_schema_json |
Text (JSON) | Yes | Company-specific intake schema mapping |
operational_mapping_json |
Text (JSON) | Yes | Operational field mappings from intake |
evidence_trace_json |
Text (JSON) | Yes | Evidence gathered during intake chat |
root_cause_hypothesis_json |
Text (JSON) | Yes | AI-generated root cause hypotheses |
compliance_flags_json |
Text (JSON) | Yes | Regulatory/compliance flags raised |
classification_audit_json |
Text (JSON) | Yes | Full audit trail of classification decisions |
Relationships:
- Has one
classificationsrecord (viacase_id) - Has one
risk_assessmentsrecord (viacase_id) - Has one
resolutionsrecord (viacase_id)
AI classification output. Created by the classification agent after intake is finalised.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
String(32) | No | UUID hex, primary key |
case_id |
String(32) | No | FK → complaint_cases.id
|
product_category |
String(60) | No | Top-level product category |
issue_type |
String(60) | No | Specific issue type |
sub_issue |
String(120) | Yes | Subcategory |
confidence |
Float | No | Model confidence, 0.0 – 1.0 |
reasoning |
Text | Yes | LLM explanation of classification decision |
review_recommended |
Boolean | Yes | Flag for human review queue |
reason_codes_json |
Text (JSON) | Yes | Coded reason list |
keywords_json |
Text (JSON) | Yes | Keywords extracted from the narrative |
created_at |
DateTime | — | Auto-set on insert |
AI risk scoring output. Created by the risk agent.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
String(32) | No | UUID hex, primary key |
case_id |
String(32) | No | FK → complaint_cases.id
|
risk_level |
String(20) | No |
low, medium, high, critical
|
risk_score |
Float | No | Numeric score, 0.0 – 1.0 |
regulatory_risk |
Boolean | Yes | Whether regulatory exposure was detected |
financial_impact_estimate |
Float | Yes | Estimated financial exposure |
escalation_required |
Boolean | Yes | Whether immediate escalation is needed |
reasoning |
Text | Yes | LLM explanation of risk scoring |
created_at |
DateTime | — | Auto-set on insert |
AI resolution recommendation. Created by the resolution agent.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
String(32) | No | UUID hex, primary key |
case_id |
String(32) | No | FK → complaint_cases.id
|
recommended_action |
String(40) | No |
refund, escalate, dismiss, mediate, etc. |
description |
Text | No | Full resolution narrative |
estimated_resolution_days |
Float | Yes | Expected days to resolution |
monetary_amount |
Float | Yes | Compensation amount if applicable |
confidence |
Float | Yes | Model confidence, 0.0 – 1.0 |
reasoning |
Text | Yes | LLM explanation of resolution recommendation |
created_at |
DateTime | — | Auto-set on insert |
Tracks the live state of an intake chat session. One session per complaint submission attempt.
| Column | Type | Nullable | Description |
|---|---|---|---|
session_id |
String(32) | No | Primary key |
channel |
String(20) | No | e.g. web_chat
|
company_id |
String(64) | Yes | Company context for the session |
turn_index |
Integer | No | Number of conversation turns completed |
packet_json |
Text (JSON) | No | Structured data packet built during intake |
last_agent_message |
Text | No | Most recent agent message |
last_user_message |
Text | No | Most recent user message |
conversation_history_json |
Text (JSON) | No | Full ordered chat history |
completed |
Boolean | No | Whether intake has been finalised |
handoff_triggered |
Boolean | No | Whether case creation has been triggered |
created_at |
DateTime | — | Auto-set on insert |
updated_at |
DateTime | — | Auto-updated on every write |
One row per LangGraph agent execution. Provides a durable audit trail of every AI pipeline run.
| Column | Type | Nullable | Description |
|---|---|---|---|
run_id |
String(64) | No | Primary key |
case_id |
String(32) | Yes | FK reference to the complaint case (indexed) |
company_id |
String(64) | No | Company context |
trace_id |
String(64) | Yes | LangSmith / observability trace ID (indexed) |
started_at |
DateTime | No | Run start time |
ended_at |
DateTime | Yes | Run end time |
run_status |
String(40) | No |
running, completed, failed, escalated
|
final_route |
String(120) | Yes | Final routing decision |
final_severity |
String(40) | Yes | Final severity classification |
manual_review_required |
Boolean | Yes | |
retry_count_total |
Integer | Yes | Total retries across all steps |
token_total |
Integer | Yes | Total tokens consumed |
cost_estimate_total |
Float | Yes | Estimated cost in USD |
workflow_version |
String(32) | Yes | Version of the workflow graph |
prompt_version |
String(64) | Yes | Version of the prompt set used |
knowledge_pack_version |
String(64) | Yes | Version of the RAG knowledge pack |
model_version |
String(64) | Yes | LLM model identifier used |
One row per LangGraph node execution within a run. Child of workflow_runs.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
String(32) | No | UUID hex, primary key |
run_id |
String(64) | No | FK → workflow_runs.run_id (indexed) |
node_name |
String(64) | No | e.g. supervisor, classifier, risk_agent (indexed) |
sequence_number |
Integer | No | Order of execution within the run |
started_at |
DateTime | No | Step start time |
ended_at |
DateTime | Yes | Step end time |
latency_ms |
Float | Yes | Execution time in milliseconds |
status |
String(32) | No |
success, failure, escalated, partially_completed
|
retry_number |
Integer | Yes | Retry attempt number |
model_name |
String(80) | Yes | LLM model used for this step |
input_snapshot_json |
Text (JSON) | Yes | State snapshot going into the node |
output_snapshot_json |
Text (JSON) | Yes | State snapshot coming out of the node |
state_diff_json |
Text (JSON) | Yes | Delta between input and output state |
confidence |
Float | Yes | Node-level confidence score |
error_type |
String(120) | Yes | Error class name if step failed |
error_message |
Text | Yes | Full error message if step failed |
Historical CFPB complaint narratives stored as vector embeddings. Used by the classification and risk agents for retrieval-augmented generation (RAG).
| Column | Type | Description |
|---|---|---|
id |
Integer | Auto-increment primary key |
complaint_id |
String(20) | Original CFPB complaint ID |
content |
Text | Text chunk sent to the LLM |
embedding |
Vector(384 or 1536) | pgvector embedding — HNSW cosine index |
product |
String(120) | Pre-filter metadata (indexed) |
sub_product |
String(120) | |
issue |
String(120) | Pre-filter metadata (indexed) |
sub_issue |
String(120) | |
company |
String(200) | Pre-filter metadata (indexed) |
state |
String(2) | Pre-filter metadata (indexed) |
zip_code |
String(5) | |
date_received |
String(10) | |
submitted_via |
String(20) | |
created_at |
DateTime | Auto-set on insert |
Index: HNSW with
m=16,ef_construction=64, cosine similarity (vector_cosine_ops)
Historical complaint + resolution outcome pairs stored as vector embeddings. Used by the resolution agent to retrieve similar past resolutions as precedent.
| Column | Type | Description |
|---|---|---|
id |
Integer | Auto-increment primary key |
complaint_id |
String(20) | Original CFPB complaint ID |
content |
Text | Text chunk sent to the LLM |
embedding |
Vector(384 or 1536) | pgvector embedding — HNSW cosine index |
product |
String(120) | Pre-filter metadata (indexed) |
issue |
String(120) | Pre-filter metadata (indexed) |
company |
String(200) | Pre-filter metadata (indexed) |
resolution_outcome |
String(100) | Historical outcome label (indexed) |
date_received |
String(10) | |
created_at |
DateTime | Auto-set on insert |
Index: HNSW with
m=16,ef_construction=64, cosine similarity (vector_cosine_ops)
The vector size is determined by the configured embedding model at startup:
| Model | Dimension |
|---|---|
BAAI/bge-small-en-v1.5 (local, free) |
384 |
text-embedding-3-small (OpenAI) |
1536 |