Database Schema Documentation
This document outlines the current PostgreSQL database schema for the ChainAlign project, as of November 17, 2025 (updated with migration cleanup and best practices).
Migration Management Best Practices
Overview
ChainAlign uses Knex.js for database migrations. Migrations live in backend/migrations/ and track schema changes over time. Understanding how migrations work is critical to avoid breaking production databases.
How Knex Tracks Migrations
Knex stores migration state in the knex_migrations table:
SELECT name, migration_time FROM knex_migrations ORDER BY id;
CRITICAL: Knex tracks migrations by filename, not content. Renaming a file means:
- The old name appears as "deleted/missing"
- The new name appears as "pending"
- This can cause migrations to run twice, breaking the database
Migration File Naming Convention
Format: YYYYMMDDHHMMSS_description.cjs
20251117143000_create_users_table.cjs
├── Year: 2025
├── Month: 11 (November)
├── Day: 17
├── Hour: 14 (2 PM)
├── Minute: 30
├── Second: 00
└── Description: create_users_table
Rules:
- Always use 14-digit timestamps (YYYYMMDDHHMMSS)
- Timestamps MUST be unique - never reuse timestamps
- Use snake_case for descriptions:
create_users_table.cjs - Use
.cjsextension - Knex requires CommonJS format - Be descriptive:
create_,add_,alter_,drop_,update_
Creating New Migrations
ALWAYS generate migrations using Knex CLI:
# Generate with proper timestamp
npx knex migrate:make create_new_table --env development
# This creates: 20251117143052_create_new_table.cjs
NEVER manually create migration files - this risks timestamp collisions.
Migration File Structure
// backend/migrations/20251117143000_create_example_table.cjs
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.up = async function(knex) {
await knex.schema.createTable('example_table', (table) => {
// Primary key
table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
// REQUIRED: Multi-tenancy
table.uuid('tenant_id').notNullable();
// Your columns
table.string('name', 255).notNullable();
table.text('description');
table.jsonb('metadata').defaultTo('{}');
// REQUIRED: Timestamps
table.timestamp('created_at', { useTz: true }).notNullable().defaultTo(knex.fn.now());
table.timestamp('updated_at', { useTz: true }).notNullable().defaultTo(knex.fn.now());
// REQUIRED: Foreign keys
table.foreign('tenant_id').references('tenant_id').inTable('tenants').onDelete('CASCADE');
// Indexes
table.index(['tenant_id']);
});
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = async function(knex) {
await knex.schema.dropTableIfExists('example_table');
};
Required Table Standards
Every table MUST have:
- Multi-tenancy:
tenant_idcolumn with foreign key totenants - Timestamps:
created_atandupdated_atcolumns - UUID primary keys: Use
gen_random_uuid()(notuuid_generate_v4()) - Foreign key cascades: Define
ON DELETEbehavior - Indexes: On
tenant_idand commonly queried columns
Running Migrations
# List pending migrations
npx knex migrate:list --env development
# Run all pending migrations
npx knex migrate:latest --env development
# Rollback last batch
npx knex migrate:rollback --env development
# Check migration status
npx knex migrate:status --env development
Environment Variables
Migrations require database configuration:
export DB_HOST=localhost
export DB_PORT=54322
export DB_USER=postgres
export DB_PASSWORD=postgres
export DB_NAME=chainalign_dev
# Then run migrations
npx knex migrate:latest --knexfile backend/knexfile.cjs --env development
Common Mistakes to Avoid
| Mistake | Impact | Prevention |
|---|---|---|
| Duplicate timestamps | Non-deterministic execution order | Always use Knex CLI to generate |
| Renaming migration files | Breaks all databases that already ran them | Never rename after committing |
Missing tenant_id | Multi-tenancy violation, data leakage | Use the template above |
| Incomplete timestamps | 20251105_ instead of 20251105143000_ | Always use 14 digits |
| Manual file creation | Risk of conflicts | Use npx knex migrate:make |
Missing down() function | Can't rollback schema changes | Always implement rollback |
| Raw SQL without checks | Errors on re-run | Use IF EXISTS/IF NOT EXISTS |
Deployment Strategy
When deploying migrations to production (Supabase):
- Before deploy: Check
knex_migrationstable for current state - If renaming files was necessary: Run remediation SQL first
- Deploy code: Push updated migration files
- Run migrations:
npx knex migrate:latest - Verify: Check for errors, validate table structures
Remediation for Renamed Migrations
If migrations were renamed after being run in production:
-- Update the knex_migrations table to reflect new names
UPDATE knex_migrations
SET name = 'NEW_FILENAME.cjs'
WHERE name = 'OLD_FILENAME.cjs';
See backend/migrations/MIGRATION_REMEDIATION_2025_11_17.sql for the complete remediation script from the November 2025 cleanup.
Best Practices Summary
- ✅ Use
npx knex migrate:maketo create migrations - ✅ Always include
tenant_id,created_at,updated_at - ✅ Use
gen_random_uuid()for UUIDs - ✅ Implement both
up()anddown()functions - ✅ Test migrations locally before deploying
- ✅ Never rename migration files after committing
- ✅ Document complex migrations with comments
- ✅ Use transactions for multi-step changes
- ❌ Never delete migrations that have been run
- ❌ Never modify already-run migration files
- ❌ Don't create migrations manually
Table: public.audit_log
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+-------------
audit_log_id | bigint | | not null | nextval('audit_log_audit_log_id_seq'::regclass) | plain | | |
tenant_id | uuid | | not null | | plain | | |
user_id | uuid | | not null | | plain | | |
action_type | character varying(100) | | not null | | extended | | |
details | jsonb | | | | extended | | |
rationale | text | | | | extended | | | Optional free-text explanation for the action.
token_usage | integer | | | 0 | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"audit_log_pkey" PRIMARY KEY, btree (audit_log_id)
Foreign-key constraints:
"audit_log_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"audit_log_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Table: public.connections
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
name | character varying(255) | | not null | | extended | | |
type | character varying(50) | | not null | | extended | | |
config | jsonb | | not null | | extended | | |
status | character varying(50) | | not null | 'pending'::character varying | extended | | |
last_sync_at | timestamp with time zone | | | | plain | | |
last_sync_status | character varying(50) | | | | extended | | |
last_error | text | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"connections_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"connections_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.corporate_objectives
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('corporate_objectives_id_seq'::regclass) | plain | | |
name | character varying(255) | | not null | | extended | | |
description | text | | | | extended | | |
target_value | real | | not null | | plain | | |
timeframe | character varying(255) | | | | extended | | |
weight | real | | not null | | plain | | |
status | character varying(50) | | | | extended | | |
created_by | character varying(255) | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"corporate_objectives_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "objective_mappings" CONSTRAINT "objective_mappings_objective_id_foreign" FOREIGN KEY (objective_id) REFERENCES corporate_objectives(id) ON DELETE CASCADE
Table: public.core_entities
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+------------------------------------------------
entity_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
entity_type | character varying(100) | | not null | | extended | | | e.g., 'PRODUCT', 'COST_CENTER', 'JOB_ROLE'
entity_name | character varying(255) | | not null | | extended | | |
sku | character varying(100) | | | | extended | | |
unit_cost | numeric(12,4) | | | | main | | |
unit_price | numeric(12,4) | | | | main | | |
lifecycle_status | character varying(50) | | | | extended | | |
domain_attributes | jsonb | | | | extended | | | Stores flexible attributes like product_family, etc.
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"core_entities_pkey" PRIMARY KEY, btree (entity_id)
"core_entities_entity_type_index" btree (entity_type)
"core_entities_tenant_id_entity_type_sku_unique" UNIQUE CONSTRAINT, btree (tenant_id, entity_type, sku)
Foreign-key constraints:
"core_entities_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Referenced by:
TABLE "entity_relationships" CONSTRAINT "entity_relationships_source_entity_id_foreign" FOREIGN KEY (source_entity_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
TABLE "entity_relationships" CONSTRAINT "entity_relationships_target_entity_id_foreign" FOREIGN KEY (target_entity_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
TABLE "insights" CONSTRAINT "insights_product_id_foreign" FOREIGN KEY (product_id) REFERENCES core_entities(entity_id) ON DELETE SET NULL
TABLE "sop_plan_data" CONSTRAINT "sop_plan_data_product_id_foreign" FOREIGN KEY (product_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
Table: public.csv_mapping_templates
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
name | character varying(255) | | not null | | extended | | |
mappings | jsonb | | not null | | extended | | |
user_id | uuid | | | | plain | | |
tenant_id | uuid | | not null | | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"csv_mapping_templates_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"csv_mapping_templates_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"csv_mapping_templates_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Table: public.curation_queue
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+-------------
item_id | integer | | not null | nextval('curation_queue_item_id_seq'::regclass) | plain | | |
content | text | | not null | | extended | | |
ai_summary | text | | | | extended | | |
ai_tags | jsonb | | | '{}'::jsonb | extended | | |
status | character varying(50) | | not null | 'pending'::character varying | extended | | |
source_url | character varying(2048) | | | | extended | | |
rejection_reason | text | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"curation_queue_pkey" PRIMARY KEY, btree (item_id)
Table: public.data_elements
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------------+--------------------------+-----------+----------+---------------------------------------------------+----------+-------------+--------------+-------------
element_id | integer | | not null | nextval('data_elements_element_id_seq'::regclass) | plain | | |
element_name | character varying(255) | | not null | | extended | | |
category | character varying(100) | | | | extended | | |
description | text | | | | extended | | |
data_owner | character varying(100) | | | | extended | | |
update_frequency | character varying(50) | | | | extended | | |
constituent_data | jsonb | | | | extended | | |
erp_mappings | jsonb | | | | extended | | |
business_impact | character varying(50) | | | | extended | | |
implementation_difficulty | character varying(50) | | | | extended | | |
priority_rationale | text | | | | extended | | |
priority_group | character varying(50) | | | 'Nice-to-Have'::character varying | extended | | |
source_systems | text[] | | | | extended | | |
calculation_logic | text | | | | extended | | |
data_quality_score | numeric(3,2) | | | '1'::numeric | main | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"data_elements_pkey" PRIMARY KEY, btree (element_id)
"data_elements_category_index" btree (category)
"data_elements_element_name_unique" UNIQUE CONSTRAINT, btree (element_name)
"data_elements_priority_group_index" btree (priority_group)
Table: public.data_element_embeddings
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
element_id | integer | | not null | | plain | | |
element_name | text | | not null | | extended | | |
embedding | vector(768) | | not null | | external | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"data_element_embeddings_embedding_hnsw" hnsw (embedding vector_cosine_ops)
Table: public.domain_schemas
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+-------------
schema_id | integer | | not null | nextval('domain_schemas_schema_id_seq'::regclass) | plain | | |
tenant_id | uuid | | not null | | plain | | |
entity_type | character varying(100) | | not null | | extended | | |
attribute_name | character varying(100) | | not null | | extended | | |
data_type | character varying(50) | | not null | | extended | | |
is_required | boolean | | | false | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"domain_schemas_pkey" PRIMARY KEY, btree (schema_id)
"domain_schemas_tenant_id_entity_type_attribute_name_unique" UNIQUE CONSTRAINT, btree (tenant_id, entity_type, attribute_name)
Foreign-key constraints:
"domain_schemas_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.document_chunks
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
document_id | uuid | | not null | | plain | | |
chunk_text | text | | not null | | extended | | |
chunk_order | integer | | not null | | plain | | |
embedding | vector(768) | | | | external | | |
search_vector | tsvector | | | | extended | | |
metadata | jsonb | | | '{}'::jsonb | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"document_chunks_pkey" PRIMARY KEY, btree (id)
"idx_document_chunks_document_id" btree (document_id)
"idx_document_chunks_embedding" hnsw (embedding vector_cosine_ops) WITH (m='16', ef_construction='64')
"idx_document_chunks_search_vector" gin (search_vector)
Foreign-key constraints:
"document_chunks_document_id_foreign" FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
Table: public.document_embeddings
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+-----------------------------------------------+----------+-------------+--------------+-------------
document_id | uuid | | not null | | plain | | |
source_text | text | | not null | | extended | | |
source_text_hash | text | | | generated always as (md5(source_text)) stored | extended | | |
embedding | vector(768) | | not null | | external | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"document_embeddings_pkey" PRIMARY KEY, btree (document_id)
"idx_document_embeddings_source_text_hash" btree (source_text_hash)
"idx_document_embeddings_vector_cosine" hnsw (embedding vector_cosine_ops) WITH (m='16', ef_construction='64')
"idx_document_embeddings_vector_l2" hnsw (embedding vector_l2_ops)
Foreign-key constraints:
"document_embeddings_document_id_foreign" FOREIGN KEY (document_id) REFERENCES source_documents(id) ON DELETE CASCADE
Triggers:
update_document_embeddings_updated_at BEFORE UPDATE ON document_embeddings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
Table: public.documents
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
title | text | | not null | | extended | | |
type | text | | not null | | extended | | |
is_public | boolean | | | false | plain | | |
status | text | | | 'pending'::text | extended | | |
metadata | jsonb | | | '{}'::jsonb | extended | | |
user_id | uuid | | | | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"documents_pkey" PRIMARY KEY, btree (id)
"idx_documents_is_public" btree (is_public)
"idx_documents_status" btree (status)
Referenced by:
TABLE "document_chunks" CONSTRAINT "document_chunks_document_id_foreign" FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
Table: public.embedding_error_log
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+-------------
id | bigint | | not null | nextval('embedding_error_log_id_seq'::regclass) | plain | | |
document_id | text | | not null | | extended | | |
error_message | text | | not null | | extended | | |
error_context | jsonb | | | '{}'::jsonb | extended | | |
function_name | text | | | | extended | | |
queue_message_id | text | | | | extended | | |
retry_count | integer | | | 0 | plain | | |
max_retries | integer | | | 3 | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"embedding_error_log_pkey" PRIMARY KEY, btree (id)
"idx_embedding_error_log_created_at" btree (created_at)
"idx_embedding_error_log_document_id" btree (document_id)
Table: public.entity_relationships
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+------------------------------------------------------
relationship_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
source_entity_id | uuid | | not null | | plain | | |
target_entity_id | uuid | | not null | | plain | | |
relationship_type | character varying(100) | | not null | | extended | | | e.g., 'IS_CHILD_OF', 'REPORTS_TO', 'IS_LOCATED_IN'
metadata | jsonb | | | | extended | | | Stores relationship-specific attributes like quantity for a BOM.
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"entity_relationships_pkey" PRIMARY KEY, btree (relationship_id)
"entity_relationships_source_entity_id_target_entity_id_rel_index" btree (source_entity_id, target_entity_id, relationship_type)
Foreign-key constraints:
"entity_relationships_source_entity_id_foreign" FOREIGN KEY (source_entity_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
"entity_relationships_target_entity_id_foreign" FOREIGN KEY (target_entity_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
"entity_relationships_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.insight_cache
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
cache_key | text | | not null | | extended | | |
insights_data | jsonb | | not null | | extended | | |
expires_at | timestamp with time zone | | not null | | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"insight_cache_pkey" PRIMARY KEY, btree (id)
"idx_insight_cache_expires" btree (expires_at)
"idx_insight_cache_key" btree (cache_key)
"insight_cache_cache_key_unique" UNIQUE CONSTRAINT, btree (cache_key)
Table: public.insights
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
insight_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
s_op_cycle_id | uuid | | | | plain | | |
product_id | uuid | | | | plain | | |
insight_type | character varying(50) | | not null | | extended | | |
title | text | | not null | | extended | | |
content | text | | not null | | extended | | |
embedding | vector(768) | | | | external | | |
source_document_id | uuid | | | | plain | | |
created_by_user_id | uuid | | | | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"insights_pkey" PRIMARY KEY, btree (insight_id)
Foreign-key constraints:
"insights_created_by_user_id_foreign" FOREIGN KEY (created_by_user_id) REFERENCES users(user_id) ON DELETE SET NULL
"insights_product_id_foreign" FOREIGN KEY (product_id) REFERENCES core_entities(entity_id) ON DELETE SET NULL
"insights_s_op_cycle_id_foreign" FOREIGN KEY (s_op_cycle_id) REFERENCES sop_cycles(cycle_id) ON DELETE SET NULL
"insights_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.kb_ingestion_metadata
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
file_path | character varying(255) | | not null | | extended | | |
last_modified_at | timestamp with time zone | | not null | | plain | | |
last_ingested_at | timestamp with time zone | | not null | | plain | | |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP | plain | | |
Indexes:
"kb_ingestion_metadata_pkey" PRIMARY KEY, btree (file_path)
"kb_ingestion_metadata_file_path_unique" UNIQUE CONSTRAINT, btree (file_path)
Table: public.knex_migrations
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+---------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('knex_migrations_id_seq'::regclass) | plain | | |
name | character varying(255) | | | | extended | | |
batch | integer | | | | plain | | |
migration_time | timestamp with time zone | | | | plain | | |
Indexes:
"knex_migrations_pkey" PRIMARY KEY, btree (id)
Table: public.knex_migrations_lock
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+-----------------------------------------------------+---------+-------------+--------------+-------------
index | integer | | not null | nextval('knex_migrations_lock_index_seq'::regclass) | plain | | |
is_locked | integer | | | | plain | | |
Indexes:
"knex_migrations_lock_pkey" PRIMARY KEY, btree (index)
Table: public.objective_mappings
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+------------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('objective_mappings_id_seq'::regclass) | plain | | |
objective_id | integer | | not null | | plain | | |
sop_data_element | character varying(255) | | not null | | extended | | |
Indexes:
"objective_mappings_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"objective_mappings_objective_id_foreign" FOREIGN KEY (objective_id) REFERENCES corporate_objectives(id) ON DELETE CASCADE
Table: public.organizational_units
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
org_unit_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
parent_org_unit_id | uuid | | | | plain | | |
name | character varying(255) | | not null | | extended | | |
level | character varying(50) | | not null | | extended | | |
Indexes:
"organizational_units_pkey" PRIMARY KEY, btree (org_unit_id)
Foreign-key constraints:
"organizational_units_parent_org_unit_id_foreign" FOREIGN KEY (parent_org_unit_id) REFERENCES organizational_units(org_unit_id) ON DELETE SET NULL
"organizational_units_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Referenced by:
TABLE "organizational_units" CONSTRAINT "organizational_units_parent_org_unit_id_foreign" FOREIGN KEY (parent_org_unit_id) REFERENCES organizational_units(org_unit_id) ON DELETE SET NULL
TABLE "sop_cycles" CONSTRAINT "sop_cycles_org_unit_id_foreign" FOREIGN KEY (org_unit_id) REFERENCES organizational_units(org_unit_id) ON DELETE CASCADE
Table: public.permissions
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------+------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
permission_id | uuid | | not null | gen_random_uuid() | plain | | |
permission_name | character varying(100) | | not null | | extended | | |
Indexes:
"permissions_pkey" PRIMARY KEY, btree (permission_id)
"permissions_permission_name_unique" UNIQUE CONSTRAINT, btree (permission_name)
Referenced by:
TABLE "role_permissions" CONSTRAINT "role_permissions_permission_id_foreign" FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
Table: public.role_permissions
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+------+-----------+----------+---------+---------+-------------+--------------+-------------
role_id | uuid | | not null | | plain | | |
permission_id | uuid | | not null | | plain | | |
Indexes:
"role_permissions_pkey" PRIMARY KEY, btree (role_id, permission_id)
Foreign-key constraints:
"role_permissions_permission_id_foreign" FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
"role_permissions_role_id_foreign" FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
Table: public.roles
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
role_id | uuid | | not null | gen_random_uuid() | plain | | |
role_name | character varying(100) | | not null | | extended | | |
Indexes:
"roles_pkey" PRIMARY KEY, btree (role_id)
"roles_role_name_unique" UNIQUE CONSTRAINT, btree (role_name)
Referenced by:
TABLE "role_permissions" CONSTRAINT "role_permissions_role_id_foreign" FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
TABLE "user_roles" CONSTRAINT "user_roles_role_id_foreign" FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
Table: public.scenarios
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
scenario_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
name | character varying(255) | | not null | | extended | | |
subtitle | text | | | | extended | | |
insight | text | | | | extended | | |
revenue_at_risk | numeric(15,4) | | | | main | | |
working_capital | numeric(15,4) | | | | main | | |
service_level | numeric(5,2) | | | | main | | |
stockout_risk | numeric(5,2) | | | | main | | |
net_benefit | numeric(15,4) | | | | main | | |
confidence | numeric(5,2) | | | | main | | |
monte_carlo_results | jsonb | | | | extended | | |
plan_inputs | jsonb | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"scenarios_pkey" PRIMARY KEY, btree (scenario_id)
Foreign-key constraints:
"scenarios_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.sales
Purpose: Core fact table for time-series sales data. Supports forecasting, financial auditing, and Judgment Engine retrospection. Multi-tenant, time-series optimized for demand signals and financial impact analysis.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
sale_id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
tenant_id | uuid | | not null | | plain | | | Multi-tenant isolation
sale_date | timestamp with time zone | | not null | | plain | | | Critical for time-series queries and partitioning
location_id | uuid | | not null | | plain | | | FK to locations (store dimension)
entity_id | uuid | | not null | | plain | | | FK to core_entities (product/SKU)
quantity_sold | numeric(15,4) | | not null | | main | | | Core demand metric for forecasting
revenue | numeric(19,4) | | | | main | | | Total selling price (financial impact)
cost_of_goods_sold | numeric(19,4) | | | | main | | | Direct cost (gross margin calculation)
unit_price | numeric(19,4) | | | | main | | | Price at time of sale (scenario backtesting)
promo_flag | boolean | | | false | plain | | | External covariate for ML models
stockout_flag | boolean | | | false | plain | | | Demand censoring indicator (data cleaning)
event_id | uuid | | | | plain | | | FK to events (external context, nullable)
s_and_op_cycle_id | uuid | | | | plain | | | FK to sop_cycles (Judgment Engine link)
custom_attributes | jsonb | | | '{}'::jsonb | extended | | | Flexible domain extensions
notes | text | | | | extended | | | Audit trail
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"sales_pkey" PRIMARY KEY, btree (sale_id)
"idx_sales_tenant_loc_ent_date" btree (tenant_id, location_id, entity_id, sale_date)
"idx_sales_tenant_date" btree (tenant_id, sale_date)
"idx_sales_loc_ent_date" btree (location_id, entity_id, sale_date)
"idx_sales_date" btree (sale_date)
"idx_sales_promo" btree (tenant_id, promo_flag)
"idx_sales_stockout" btree (tenant_id, stockout_flag)
"idx_sales_cycle_entity" btree (s_and_op_cycle_id, entity_id)
"uniq_sales_tenant_loc_ent_date" UNIQUE CONSTRAINT, btree (tenant_id, location_id, entity_id, sale_date)
Foreign-key constraints:
"sales_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"sales_location_id_foreign" FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE CASCADE
"sales_entity_id_foreign" FOREIGN KEY (entity_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
"sales_event_id_foreign" FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL
"sales_s_and_op_cycle_id_foreign" FOREIGN KEY (s_and_op_cycle_id) REFERENCES sop_cycles(cycle_id) ON DELETE SET NULL
Table: public.search_history
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
user_id | uuid | | | | plain | | |
query | text | | not null | | extended | | |
direct_answer | text | | | | extended | | |
related_questions | jsonb | | | | extended | | |
clicked_documents | uuid[] | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"search_history_pkey" PRIMARY KEY, btree (id)
"idx_search_history_created_at" btree (created_at)
"idx_search_history_user_id" btree (user_id)
Table: public.sop_cycles
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+-----------------------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------
cycle_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
org_unit_id | uuid | | not null | | plain | | |
cycle_period | date | | not null | | plain | | |
status | character varying(50) | | not null | 'In Progress'::character varying | extended | | |
version | integer | | not null | 1 | plain | | |
Indexes:
"sop_cycles_pkey" PRIMARY KEY, btree (cycle_id)
Foreign-key constraints:
"sop_cycles_org_unit_id_foreign" FOREIGN KEY (org_unit_id) REFERENCES organizational_units(org_unit_id) ON DELETE CASCADE
"sop_cycles_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Referenced by:
TABLE "insights" CONSTRAINT "insights_s_op_cycle_id_foreign" FOREIGN KEY (s_op_cycle_id) REFERENCES sop_cycles(cycle_id) ON DELETE SET NULL
TABLE "sop_plan_data" CONSTRAINT "sop_plan_data_cycle_id_foreign" FOREIGN KEY (cycle_id) REFERENCES sop_cycles(cycle_id) ON DELETE CASCADE
Table: public.sop_plan_data
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------------+--------------------------+-----------+----------+-----------------------------------------------------+---------+-------------+--------------+-------------
plan_data_id | bigint | | not null | nextval('sop_plan_data_plan_data_id_seq'::regclass) | plain | | |
cycle_id | uuid | | not null | | plain | | |
product_id | uuid | | not null | | plain | | |
tenant_id | uuid | | not null | | plain | | |
period | date | | not null | | plain | | |
bookings_plan | integer | | | | plain | | |
bookings_actual | integer | | | | plain | | |
shipments_plan | integer | | | | plain | | |
shipments_actual | integer | | | | plain | | |
backlog_open | integer | | | | plain | | |
backlog_close | integer | | | | plain | | |
production_plan | integer | | | | plain | | |
production_actual | integer | | | | plain | | |
inventory_plan | integer | | | | plain | | |
inventory_actual | integer | | | | plain | | |
statistical_forecast | integer | | | | plain | | |
consensus_demand_plan | integer | | | | plain | | |
capacity_plan | integer | | | | plain | | |
safety_stock_target | integer | | | | plain | | |
revenue_plan | numeric(15,4) | | | | main | | |
revenue_actual | numeric(15,4) | | | | main | | |
cogs_plan | numeric(15,4) | | | | main | | |
cogs_actual | numeric(15,4) | | | | main | | |
source_creation_timestamp | timestamp with time zone | | | | plain | | |
opspilot_sync_timestamp | timestamp with time zone | | | CURRENT_TIMESTAMP | plain | | |
Indexes:
"sop_plan_data_pkey" PRIMARY KEY, btree (plan_data_id)
"sop_plan_data_cycle_id_product_id_period_unique" UNIQUE CONSTRAINT, btree (cycle_id, product_id, period)
Foreign-key constraints:
"sop_plan_data_cycle_id_foreign" FOREIGN KEY (cycle_id) REFERENCES sop_cycles(cycle_id) ON DELETE CASCADE
"sop_plan_data_product_id_foreign" FOREIGN KEY (product_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
"sop_plan_data_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.source_documents
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
content | text | | not null | | extended | | |
metadata | jsonb | | | '{}'::jsonb | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"source_documents_pkey" PRIMARY KEY, btree (id)
"idx_source_documents_content_fts" gin (to_tsvector('english'::regconfig, content))
"idx_source_documents_created_at" btree (created_at)
"idx_source_documents_metadata_gin" gin (metadata)
"idx_source_documents_updated_at" btree (updated_at)
Referenced by:
TABLE "document_embeddings" CONSTRAINT "document_embeddings_document_id_foreign" FOREIGN KEY (document_id) REFERENCES source_documents(id) ON DELETE CASCADE
Triggers:
enqueue_embedding_on_change AFTER INSERT OR UPDATE ON source_documents FOR EACH ROW EXECUTE FUNCTION enqueue_embedding_job()
update_source_documents_updated_at BEFORE UPDATE ON source_documents FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
Table: public.sources
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
source_id | uuid | | not null | gen_random_uuid() | plain | | |
url | character varying(255) | | not null | | extended | | |
type | character varying(255) | | not null | | extended | | |
last_fetched | timestamp with time zone | | | | plain | | |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP | plain | | |
Indexes:
"sources_pkey" PRIMARY KEY, btree (source_id)
"sources_url_unique" UNIQUE CONSTRAINT, btree (url)
Table: public.tenants
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
tenant_id | uuid | | not null | gen_random_uuid() | plain | | |
company_name | character varying(255) | | not null | | extended | | |
full_name | character varying(255) | | | | extended | | |
address | character varying(255) | | | | extended | | |
admin_name | character varying(255) | | | | extended | | |
admin_email | character varying(255) | | | | extended | | |
duns_number | character varying(255) | | | | extended | | |
industry | character varying(255) | | | | extended | | |
creator_user_id | uuid | | | | plain | | |
creator_email | character varying(255) | | | | extended | | |
creator_display_name | character varying(255) | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"tenants_pkey" PRIMARY KEY, btree (tenant_id)
Referenced by:
TABLE "audit_log" CONSTRAINT "audit_log_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "csv_mapping_templates" CONSTRAINT "csv_mapping_templates_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "insights" CONSTRAINT "insights_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "organizational_units" CONSTRAINT "organizational_units_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "scenarios" CONSTRAINT "scenarios_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "sop_cycles" CONSTRAINT "sop_cycles_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "sop_plan_data" CONSTRAINT "sop_plan_data_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "user_profiles" CONSTRAINT "user_profiles_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
TABLE "users" CONSTRAINT "users_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.user_profiles
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
user_id | uuid | | not null | | plain | | |
tenant_id | uuid | | not null | | plain | | |
behavioral_patterns | jsonb | | | | extended | | |
learning_metadata | jsonb | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"user_profiles_pkey" PRIMARY KEY, btree (id)
"user_profiles_user_id_tenant_id_unique" UNIQUE CONSTRAINT, btree (user_id, tenant_id)
Foreign-key constraints:
"user_profiles_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"user_profiles_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Table: public.user_roles
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+------+-----------+----------+---------+---------+-------------+--------------+-------------
user_id | uuid | | not null | | plain | | |
role_id | uuid | | not null | | plain | | |
Indexes:
"user_roles_pkey" PRIMARY KEY, btree (user_id, role_id)
Foreign-key constraints:
"user_roles_role_id_foreign" FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE
"user_roles_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Table: public.users
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+--------------------------+-----------+----------+-------------------+----------+-------------+--------------+-------------
user_id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
email | character varying(255) | | not null | | extended | | |
password_hash | character varying(255) | | not null | | extended | | |
first_name | character varying(255) | | | | extended | | |
last_name | character varying(255) | | | | extended | | |
role | character varying(255) | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email_unique" UNIQUE CONSTRAINT, btree (email)
Foreign-key constraints:
"users_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Referenced by:
TABLE "audit_log" CONSTRAINT "audit_log_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
TABLE "csv_mapping_templates" CONSTRAINT "csv_mapping_templates_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
TABLE "insights" CONSTRAINT "insights_created_by_user_id_foreign" FOREIGN KEY (created_by_user_id) REFERENCES users(user_id) ON DELETE SET NULL
TABLE "user_profiles" CONSTRAINT "user_profiles_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
TABLE "user_roles" CONSTRAINT "user_roles_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Table: public.insight_feedback
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+-------------
feedback_id | uuid | | not null | gen_random_uuid() | plain | | |
insight_id | uuid | | | | plain | | |
user_id | uuid | | not null | | plain | | |
tenant_id | uuid | | not null | | plain | | |
page_context | jsonb | | | | extended | | |
suggested_text | text | | not null | | extended | | |
rationale | text | | | | extended | | | Optional free-text explanation for the feedback.
feedback_type | character varying(50) | | not null | | extended | | |
status | character varying(50) | | not null | 'pending_review'::character varying | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"insight_feedback_pkey" PRIMARY KEY, btree (feedback_id)
Foreign-key constraints:
"insight_feedback_insight_id_foreign" FOREIGN KEY (insight_id) REFERENCES insights(insight_id) ON DELETE SET NULL
"insight_feedback_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"insight_feedback_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Table: public.forecast_accuracy_reports
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------------------------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
forecast_id | uuid | | | | plain | | | Nullable for now, link to scenarios or future forecasts table
period | date | | not null | | plain | | |
metric_name | character varying(100) | | not null | | extended | | |
metric_value | numeric(15,4) | | | | main | | |
actual_value | numeric(15,4) | | | | main | | |
forecast_value | numeric(15,4) | | | | main | | |
comparison_details | jsonb | | | '{}'::jsonb | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"forecast_accuracy_reports_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"forecast_accuracy_reports_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.transcriptions
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
user_id | uuid | | not null | | plain | | |
tenant_id | uuid | | not null | | plain | | |
text | text | | not null | | extended | | |
metadata | jsonb | | | '{}'::jsonb | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"transcriptions_pkey" PRIMARY KEY, btree (id)
"transcriptions_user_id_tenant_id_idx" btree (user_id, tenant_id)
"transcriptions_created_at_idx" btree (created_at)
Foreign-key constraints:
"transcriptions_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
"transcriptions_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.nlp_requests
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
user_id | uuid | | not null | | plain | | |
tenant_id | uuid | | not null | | plain | | |
input_text | text | | not null | | extended | | |
structured_query | jsonb | | not null | | extended | | |
metadata | jsonb | | | '{}'::jsonb | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"nlp_requests_pkey" PRIMARY KEY, btree (id)
"nlp_requests_user_id_tenant_id_idx" btree (user_id, tenant_id)
"nlp_requests_created_at_idx" btree (created_at)
Foreign-key constraints:
"nlp_requests_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
"nlp_requests_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.data_catalog_master
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------------------------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('data_catalog_master_id_seq'::regclass) | plain | | |
field_name | character varying(255) | | not null | | extended | | |
description | text | | | | extended | | |
data_type | character varying(255) | | | | extended | | |
source_table | character varying(255) | | | | extended | | |
required_by_engine | character varying(255) | | | | extended | | |
is_mandatory | boolean | | | false | plain | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"data_catalog_master_pkey" PRIMARY KEY, btree (id)
"data_catalog_master_field_name_unique" UNIQUE CONSTRAINT, btree (field_name)
Referenced by:
TABLE "customer_data_map" CONSTRAINT "customer_data_map_catalog_id_foreign" FOREIGN KEY (catalog_id) REFERENCES data_catalog_master(id) ON DELETE CASCADE
Table: public.customer_data_map
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------+--------------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('customer_data_map_id_seq'::regclass) | plain | | |
tenant_id | uuid | | not null | | plain | | |
catalog_id | integer | | not null | | plain | | |
is_provided | boolean | | | false | plain | | |
source_system | character varying(255) | | | | extended | | |
source_field_name | character varying(255) | | | | extended | | |
last_updated_at | timestamp with time zone | | | | plain | | |
data_quality_summary | jsonb | | | | extended | | |
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"customer_data_map_pkey" PRIMARY KEY, btree (id)
"customer_data_map_tenant_id_catalog_id_unique" UNIQUE CONSTRAINT, btree (tenant_id, catalog_id)
Foreign-key constraints:
"customer_data_map_catalog_id_foreign" FOREIGN KEY (catalog_id) REFERENCES data_catalog_master(id) ON DELETE CASCADE
"customer_data_map_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.decision_problems
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | |
title | text | | not null | | extended | | |
context_text | text | | | | extended | | |
source_notes | uuid[] | | | '{}'::uuid[] | extended | | |
impact_area | text | | | | extended | | |
status | text | | not null | 'draft'::text | extended | | |
created_by | uuid | | not null | | plain | | |
created_at | timestamp with time zone | | not null | now() | plain | | |
updated_at | timestamp with time zone | | not null | now() | plain | | |
tags | text[] | | | '{}'::text[] | extended | | |
metadata | jsonb | | | '{}'::jsonb | extended | | |
Indexes:
"decision_problems_pkey" PRIMARY KEY, btree (id)
"decision_problems_status_check" CHECK (status = ANY (ARRAY['draft'::text, 'gathering_options'::text, 'ready_for_decision'::text, 'archived'::text]))
Foreign-key constraints:
"decision_problems_created_by_fkey" FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
"decision_problems_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
Table: public.notes
Purpose: Capture raw thoughts and inputs from any source (web, files, direct input, API integrations). Part of the Judgment Engine (M36) for decision intelligence.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
content | text | | not null | | extended | | | Raw note content
source_type | text | | | 'direct_input'::text | extended | | | web_paste, file_upload, direct_input, api_integration
source_origin | character varying(255) | | | | extended | | | Source URL or system identifier
source_metadata | jsonb | | | '{}'::jsonb | extended | | | Flexible source attributes
created_by | uuid | | not null | | plain | | | User who created note
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
tags | text[] | | | '{}'::text[] | extended | | | Flexible tagging
metadata | jsonb | | | '{}'::jsonb | extended | | | Free-form attributes
Indexes:
"notes_pkey" PRIMARY KEY, btree (id)
"notes_tenant_id_index" btree (tenant_id)
"notes_created_at_index" btree (created_at DESC)
"notes_created_by_index" btree (created_by)
"notes_source_origin_index" btree (source_origin)
Foreign-key constraints:
"notes_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"notes_created_by_foreign" FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE RESTRICT
M57 - TaskAlign: Task Management Tables
Purpose: Personal and team task management with session continuity, pseudo-ID generation, and milestone tracking. Supports work session checkpointing for context preservation across interruptions.
Added in Milestone M57: November 2, 2025
Table: public.tasks
Purpose: Core task entity for personal and team task management. Supports hierarchical tasks (parent-child relationships) and dependencies.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
task_id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
pseudo_id | character varying(50) | | | | extended | | | Human-readable ID (M57-001, M57-002, etc.)
title | text | | not null | | extended | | | Task title
description | text | | | | extended | | | Detailed task description
project | character varying(255) | | not null | | extended | | | Project namespace (e.g., 'chainalign', 'internal')
milestone | character varying(100) | | not null | | extended | | | Milestone identifier (e.g., 'M57', 'M58')
status | character varying(50) | | not null | 'pending'::character varying | extended | | | pending, in_progress, completed, blocked, cancelled
priority | character varying(20) | | | | extended | | | low, medium, high, critical
parent_task_id | uuid | | | | plain | | | Self-referencing FK for hierarchical tasks
depends_on | uuid[] | | | '{}'::uuid[] | extended | | | Array of task IDs this task depends on
implementation_status | jsonb | | | '{...}'::jsonb | extended | | | Tracks files_modified, fsd_sections_completed, last_checkpoint, verified
due_date | timestamp with time zone | | | | plain | | | Optional due date
started_at | timestamp with time zone | | | | plain | | | When task was started
completed_at | timestamp with time zone | | | | plain | | | When task was completed
estimated_hours | numeric(5,2) | | | | main | | | Estimated effort in hours
actual_hours | numeric(5,2) | | | | main | | | Actual time spent in hours
tags | text[] | | | '{}'::text[] | extended | | | Flexible tagging for organization
created_by_user_id | uuid | | not null | | plain | | | User who created the task
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Creation timestamp
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Last update timestamp
Indexes:
"tasks_pkey" PRIMARY KEY, btree (task_id)
"tasks_tenant_id_index" btree (tenant_id)
"tasks_milestone_index" btree (milestone)
"tasks_status_index" btree (status)
"tasks_pseudo_id_unique" UNIQUE CONSTRAINT, btree (pseudo_id)
"tasks_depends_on_index" gin (depends_on)
Foreign-key constraints:
"tasks_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"tasks_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
"tasks_created_by_user_id_fkey" FOREIGN KEY (created_by_user_id) REFERENCES users(user_id) ON DELETE CASCADE
Referenced by:
TABLE "work_sessions" CONSTRAINT "work_sessions_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE SET NULL
TABLE "task_comments" CONSTRAINT "task_comments_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
Table: public.milestones
Purpose: Milestone-level aggregation for task progress tracking and document linking. Supports FSD, dev summary, and TDD summary document attachment (Phase 2).
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------------+--------------------------+-----------+----------+--------------------+----------+-------------+--------------+-------------
milestone_id | character varying(50) | | not null | | extended | | | Primary key (e.g., 'M57', 'M58')
project | character varying(255) | | not null | | extended | | | Project namespace
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
fsd_document_id | uuid | | | | plain | | | Link to FSD document (Phase 2)
dev_summary_id | uuid | | | | plain | | | Link to dev summary document (Phase 2)
tdd_summary_id | uuid | | | | plain | | | Link to TDD summary document (Phase 2)
fsd_approved_at | timestamp with time zone | | | | plain | | | When FSD was approved
fsd_approved_by | uuid | | | | plain | | | User who approved FSD
tasks_completed | integer | | | 0 | plain | | | Count of completed tasks
tasks_total | integer | | | 0 | plain | | | Total task count
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Creation timestamp
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Last update timestamp
Indexes:
"milestones_pkey" PRIMARY KEY, btree (milestone_id)
"milestones_tenant_id_index" btree (tenant_id)
"milestones_project_index" btree (project)
Foreign-key constraints:
"milestones_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"milestones_fsd_approved_by_fkey" FOREIGN KEY (fsd_approved_by) REFERENCES users(user_id) ON DELETE SET NULL
Table: public.work_sessions
Purpose: Session checkpointing for context preservation across interruptions. Tracks where the user was working, what files were modified, and provides a resume summary for next session.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------+--------------------------+-----------+----------+--------------------+----------+-------------+--------------+-------------
session_id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
user_id | uuid | | not null | | plain | | | User who owns session
task_id | uuid | | | | plain | | | Task being worked on
milestone | character varying(50) | | | | extended | | | Milestone context
fsd_section | text | | | | extended | | | FSD section being worked on
files_in_progress | text[] | | | '{}'::text[] | extended | | | List of files being modified
last_command | text | | | | extended | | | Last executed command
cursor_position | jsonb | | | | extended | | | Editor cursor position (file, line, column)
git_branch | character varying(255) | | | | extended | | | Current git branch
git_commit_sha | character varying(40) | | | | extended | | | Current git commit SHA
session_summary | text | | | | extended | | | Summary of work done
token_budget_used | integer | | | | plain | | | Tokens consumed in session
context_preserved | jsonb | | | '{}'::jsonb | extended | | | Structured context for resume
started_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Session start time
ended_at | timestamp with time zone | | | | plain | | | Session end time
ended_reason | character varying(50) | | | | extended | | | completed, interrupted, token_limit, crash
Indexes:
"work_sessions_pkey" PRIMARY KEY, btree (session_id)
"work_sessions_user_id_index" btree (user_id)
"work_sessions_active_index" btree (user_id, ended_at) WHERE (ended_at IS NULL)
Foreign-key constraints:
"work_sessions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
"work_sessions_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE SET NULL
Table: public.task_comments
Purpose: Collaboration support for tasks. Allows users to add comments and notes to tasks with full audit trail.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+--------------------------+-----------+----------+--------------------+----------+-------------+--------------+-------------
comment_id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
task_id | uuid | | not null | | plain | | | Task being commented on
user_id | uuid | | not null | | plain | | | User who wrote comment
content | text | | not null | | extended | | | Comment text
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Creation timestamp
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | Last update timestamp
Indexes:
"task_comments_pkey" PRIMARY KEY, btree (comment_id)
"task_comments_task_id_index" btree (task_id)
"task_comments_user_id_index" btree (user_id)
Foreign-key constraints:
"task_comments_task_id_fkey" FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
"task_comments_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Database Functions (M57)
generate_pseudo_id(p_milestone VARCHAR, p_tenant_id UUID)
Purpose: Generates tenant and milestone-scoped sequential IDs in format M57-001, M57-002, etc.
Parameters:
p_milestone: Milestone identifier (e.g., 'M57')p_tenant_id: Tenant UUID for scoping
Returns: VARCHAR - Next sequential pseudo-ID for the milestone
Usage:
SELECT generate_pseudo_id('M57', tenant_id) INTO pseudo_id;
Implementation: Loop-based with race condition handling via EXISTS check
Table: public.scenarios
Purpose: Store alternative solutions/options for a decision problem. Each scenario represents a distinct approach with predicted impacts and confidence levels.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
scenario_id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
decision_problem_id| uuid | | | | plain | | | Links to decision_problems table
name | character varying(255) | | not null | | extended | | | Scenario name/title
description | text | | | | extended | | | Detailed scenario description
predicted_impact | jsonb | | | '{}'::jsonb | extended | | | Impact prediction (effort, risk, learning_value, custom fields)
confidence | numeric(5,2) | | | 50.00 | main | | | Confidence percentage (0-100)
created_by | uuid | | not null | | plain | | | User who created scenario
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
metadata | jsonb | | | '{}'::jsonb | extended | | | Free-form scenario attributes
Indexes:
"scenarios_pkey" PRIMARY KEY, btree (scenario_id)
"scenarios_tenant_id_index" btree (tenant_id)
"scenarios_decision_problem_id_index" btree (decision_problem_id)
"scenarios_created_at_index" btree (created_at DESC)
Foreign-key constraints:
"scenarios_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"scenarios_decision_problem_id_foreign" FOREIGN KEY (decision_problem_id) REFERENCES decision_problems(id) ON DELETE CASCADE
"scenarios_created_by_foreign" FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE RESTRICT
Table: public.decisions
Purpose: Immutable record of final decisions made. Each decision records the chosen scenario for a decision problem, the decision maker, and the final rationale. IMMUTABLE - no updates allowed.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | | Primary key
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
decision_problem_id | uuid | | not null | | plain | | | Links to decision_problems
chosen_scenario_id | uuid | | not null | | plain | | | The selected scenario
decision_maker_id | uuid | | not null | | plain | | | User who made decision
final_rationale | text | | not null | | extended | | | Why this scenario was chosen
decided_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | | When decision was made
actual_outcome | jsonb | | | '{}'::jsonb | extended | | | Future: outcome recording
metadata | jsonb | | | '{}'::jsonb | extended | | | Free-form decision attributes
Indexes:
"decisions_pkey" PRIMARY KEY, btree (id)
"decisions_tenant_id_index" btree (tenant_id)
"decisions_decision_problem_id_index" btree (decision_problem_id)
"decisions_decided_at_index" btree (decided_at DESC)
Foreign-key constraints:
"decisions_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"decisions_decision_problem_id_foreign" FOREIGN KEY (decision_problem_id) REFERENCES decision_problems(id) ON DELETE CASCADE
"decisions_chosen_scenario_id_foreign" FOREIGN KEY (chosen_scenario_id) REFERENCES scenarios(scenario_id) ON DELETE CASCADE
"decisions_decision_maker_id_foreign" FOREIGN KEY (decision_maker_id) REFERENCES users(user_id) ON DELETE RESTRICT
**IMMUTABILITY**: Decisions are append-only. No UPDATE or DELETE operations allowed on this table. Audit trail is guaranteed.
Judgment Engine (M36) Flow
The Judgment Engine uses these four tables in sequence:
- Notes → Raw inputs from any source
- Decision Problems → Structured problems extracted from notes
- Scenarios → Alternative solutions evaluated for each problem
- Decisions → Final immutable choice recorded
Key Design Principles:
- Multi-tenant isolation via
tenant_idon every table - Immutable decisions for audit trail compliance
- Flexible JSON for predicted impacts and attributes
- Soft relationships (notes → decision_problems via arrays, hard FKs elsewhere)
- Full lineage tracking from raw note to final decision
Policy Management Tables
Three tables for comprehensive policy tracking (external, internal, regulatory):
Table: public.policy_events
Purpose: Track policy events affecting supply chain (tariffs, FTAs, sanctions, regulations, internal decisions). Distinguishes between external government policies, internal company decisions, and regulatory compliance requirements.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
policy_type | text | | not null | | extended | | | EXTERNAL, INTERNAL, REGULATORY
event_type | character varying(50) | | not null | | extended | | | TARIFF_CHANGE, FTA_SIGNED, SANCTIONS, QUOTA_CHANGE, REGULATION_CHANGE, EXCHANGE_RATE_SHOCK, SUPPLIER_AGREEMENT, etc.
description | text | | not null | | extended | | | Detailed description of policy
announced_date | date | | | | plain | | | When policy was announced
event_date | date | | not null | | plain | | | When policy takes/took effect
effective_date | date | | | | plain | | | When implementation begins
expiry_date | date | | | | plain | | | When policy expires (if applicable)
impact_magnitude | character varying(255) | | | | extended | | | e.g., "25% tariff", "7-day lead time increase"
affected_countries | jsonb | | | '[]'::jsonb | extended | | | Array of ISO country codes
affected_hs_codes | jsonb | | | '[]'::jsonb | extended | | | Array of Harmonized System product codes
affected_products | jsonb | | | '[]'::jsonb | extended | | | Array of product entity IDs
affected_locations | jsonb | | | '[]'::jsonb | extended | | | Array of location IDs
source_url | text | | | | extended | | | Official announcement URL
regulatory_reference | text | | | | extended | | | Regulation ID (for regulatory policies)
user_notes | text | | | | extended | | | Internal notes
created_by | uuid | | not null | | plain | | | User who created event
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"policy_events_pkey" PRIMARY KEY, btree (id)
"idx_policy_events_tenant_type" btree (tenant_id, policy_type)
"idx_policy_events_tenant_date" btree (tenant_id, event_date)
"idx_policy_events_type" btree (event_type)
"idx_policy_events_policy_type" btree (policy_type)
Foreign-key constraints:
"policy_events_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"policy_events_created_by_foreign" FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE RESTRICT
Table: public.supplier_routing_history
Purpose: Track supply chain routing changes driven by policy events. Records when suppliers change destination countries due to tariffs, FTAs, sanctions, regulations, or internal decisions.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
supplier_id | uuid | | | | plain | | | Supplier entity
sku_id | uuid | | | | plain | | | Product affected
old_destination | character varying(10) | | not null | | extended | | | ISO country code (from)
new_destination | character varying(10) | | not null | | extended | | | ISO country code (to)
reason | character varying(255) | | | | extended | | | TARIFF_AVOIDANCE, FTA_OPTIMIZATION, COMPLIANCE, COST_REDUCTION, etc.
policy_event_id | uuid | | | | plain | | | Linked policy event (if any)
volume | numeric(15,2) | | | | main | | | Annual volume affected (units)
cost_impact | numeric(8,2) | | | | main | | | Cost change (% or absolute)
lead_time_impact | integer | | | | plain | | | Lead time change (days)
notes | text | | | | extended | | | Additional context
change_date | date | | | CURRENT_TIMESTAMP | plain | | | When routing changed
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"supplier_routing_history_pkey" PRIMARY KEY, btree (id)
"idx_routing_tenant_supplier" btree (tenant_id, supplier_id)
"idx_routing_sku_date" btree (sku_id, change_date)
"idx_routing_policy" btree (policy_event_id)
"idx_routing_date" btree (change_date)
Foreign-key constraints:
"supplier_routing_history_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"supplier_routing_history_sku_id_foreign" FOREIGN KEY (sku_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
"supplier_routing_history_policy_event_id_foreign" FOREIGN KEY (policy_event_id) REFERENCES policy_events(id) ON DELETE SET NULL
Table: public.policy_compliance_records
Purpose: Track regulatory compliance against policy requirements. Critical for pharma, food, electronics, and other regulated industries. Records compliance status for products/locations against specific regulatory policies.
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
id | uuid | | not null | gen_random_uuid() | plain | | |
tenant_id | uuid | | not null | | plain | | | Multi-tenant scoping
policy_event_id | uuid | | not null | | plain | | | Regulatory policy
product_id | uuid | | | | plain | | | Product being evaluated (null = location-wide)
location_id | uuid | | | | plain | | | Location being evaluated (null = product-specific)
supplier_id | uuid | | | | plain | | | Supplier being evaluated
compliance_status | text | | not null | 'PENDING'::text | extended | | | COMPLIANT, NON_COMPLIANT, PENDING, WAIVED, EXEMPTED
status_reason | text | | | | extended | | | Why it's in this status
remediation_actions | text | | | | extended | | | What needs to be done
remediation_deadline | date | | | | plain | | | By when it must be compliant
remediation_completed_at | date | | | | plain | | | When remediation was completed
verification_method | text | | | | extended | | | How compliance was verified (audit, certification, test, etc.)
verified_at | date | | | | plain | | | When compliance was verified
verified_by | uuid | | | | plain | | | User who verified
audit_count | integer | | | 0 | plain | | | How many times audited
audit_history | jsonb | | | '[]'::jsonb | extended | | | History of status changes
notes | text | | | | extended | | | General notes
evidence_url | text | | | | extended | | | URL to supporting documentation
metadata | jsonb | | | '{}'::jsonb | extended | | | Flexible compliance-specific data
created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
updated_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP | plain | | |
Indexes:
"policy_compliance_records_pkey" PRIMARY KEY, btree (id)
"idx_compliance_tenant_status" btree (tenant_id, compliance_status)
"idx_compliance_policy_status" btree (policy_event_id, compliance_status)
"idx_compliance_product_status" btree (product_id, compliance_status)
"idx_compliance_supplier_status" btree (supplier_id, compliance_status)
"idx_compliance_deadline" btree (remediation_deadline)
Foreign-key constraints:
"policy_compliance_records_tenant_id_foreign" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE
"policy_compliance_records_policy_event_id_foreign" FOREIGN KEY (policy_event_id) REFERENCES policy_events(id) ON DELETE CASCADE
"policy_compliance_records_product_id_foreign" FOREIGN KEY (product_id) REFERENCES core_entities(entity_id) ON DELETE CASCADE
"policy_compliance_records_location_id_foreign" FOREIGN KEY (location_id) REFERENCES locations(location_id) ON DELETE CASCADE
Table: public.decision_tasks
Purpose: Captures the execution artifact of a decision, forming the core of the Decision Loop.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| task_id | uuid | not null | gen_random_uuid() | Primary key for the task. |
| tenant_id | uuid | not null | Foreign key to tenants.tenant_id. | |
| origin_decision_id | uuid | not null | Foreign key to decisions.decision_id, linking execution to intent. | |
| constraint_id | uuid | Foreign key to constraints.constraint_id (if applicable). | ||
| task_type | character varying(50) | not null | TOC Framework: ELEVATE, EXPLOIT, SUBORDINATE, AUDIT. | |
| priority_score | numeric(5,2) | Calculated impact score from M48 analysis. | ||
| status | character varying(50) | not null | 'PENDING'::character varying | Workflow status: PENDING, IN_PROGRESS, COMPLETED, BLOCKED. |
| is_external_synced | boolean | false | Flag indicating if synced to an external system like Linear. | |
| external_ref_id | character varying(255) | ID from the external system (e.g., 'CHA-350'). | ||
| completion_notes | text | Qualitative feedback on task completion. | ||
| title | text | not null | The title of the task. | |
| description | text | Detailed description of the task. | ||
| priority | integer | Manual priority setting. | ||
| due_date | timestamp with time zone | Target completion date. | ||
| assignee_id | uuid | Foreign key to users.user_id. | ||
| metadata | jsonb | Flexible JSONB for additional attributes. | ||
| created_at | timestamp with time zone | not null | now() | |
| updated_at | timestamp with time zone | not null | now() |
Indexes: "decision_tasks_pkey" PRIMARY KEY, btree (task_id) Foreign-key constraints: "decision_tasks_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) "decision_tasks_origin_decision_id_fkey" FOREIGN KEY (origin_decision_id) REFERENCES decisions(decision_id) "decision_tasks_constraint_id_fkey" FOREIGN KEY (constraint_id) REFERENCES constraints(constraint_id) "decision_tasks_assignee_id_fkey" FOREIGN KEY (assignee_id) REFERENCES users(user_id)
Table: public.external_syncs
Purpose: Tracks the link between an internal task and its representation in an external system.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| sync_id | uuid | not null | gen_random_uuid() | Primary key. |
| task_id | uuid | not null | Foreign key to decision_tasks.task_id. | |
| system | character varying(255) | not null | The external system name (e.g., 'Linear', 'Jira'). | |
| external_id | character varying(255) | not null | The ID of the item in the external system. | |
| sync_status | character varying(255) | not null | Sync status: ACTIVE, RATE_LIMITED, FAILED, DISCONNECTED. | |
| last_synced_at | timestamp with time zone | Timestamp of the last successful sync. | ||
| metadata | jsonb | Store extra info like the external URL. |
Indexes: "external_syncs_pkey" PRIMARY KEY, btree (sync_id) "external_syncs_task_id_system_key" UNIQUE, btree (task_id, system) Foreign-key constraints: "external_syncs_task_id_fkey" FOREIGN KEY (task_id) REFERENCES decision_tasks(task_id)
Table: public.outcomes
Purpose: Captures the quantitative and qualitative results of a completed task.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| outcome_id | uuid | not null | gen_random_uuid() | Primary key. |
| task_id | uuid | not null | Foreign key to decision_tasks.task_id. | |
| metrics | jsonb | Quantitative metrics (e.g., {"cost_saved": 5000}). | ||
| narrative | text | not null | Qualitative summary of the outcome. | |
| recorded_by | uuid | not null | Foreign key to users.user_id. | |
| recorded_at | timestamp with time zone | not null | now() |
Indexes: "outcomes_pkey" PRIMARY KEY, btree (outcome_id) Foreign-key constraints: "outcomes_task_id_fkey" FOREIGN KEY (task_id) REFERENCES decision_tasks(task_id) "outcomes_recorded_by_fkey" FOREIGN KEY (recorded_by) REFERENCES users(user_id)
M58: Infisical Secrets Management Tables
The following three tables were added in M58 to implement comprehensive secrets management integration with Infisical vault:
Table: public.secrets_metadata
Purpose: Stores metadata about secrets managed via Infisical vault, including secret identification, lifecycle status, and rotation tracking.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| id | uuid | not null | gen_random_uuid() | Primary key |
| tenant_id | uuid | not null | Tenant context (required for multi-tenancy) | |
| created_by_user_id | uuid | not null | User who created this secret | |
| updated_by_user_id | uuid | not null | User who last updated this secret | |
| secret_name | character varying(255) | not null | Human-readable name of the secret | |
| infisical_secret_id | character varying(255) | Unique ID from Infisical vault (unique, nullable for drafts) | ||
| infisical_workspace_id | character varying(255) | Workspace ID in Infisical | ||
| secret_type | enum ('api_key', 'password', 'token', 'connection_string', 'encryption_key', 'certificate', 'other') | not null | 'other' | Type of secret |
| description | text | Human-readable description | ||
| tags | json | Array of tags for organization and filtering | ||
| environment | character varying(255) | not null | 'development' | Environment: dev, staging, production |
| status | enum ('active', 'rotated', 'revoked', 'expired') | not null | 'active' | Current status of the secret |
| rotation_scheduled_at | timestamp with time zone | When the next rotation is scheduled | ||
| last_rotated_at | timestamp with time zone | When the secret was last rotated | ||
| expires_at | timestamp with time zone | When the secret expires (optional TTL) | ||
| created_at | timestamp with time zone | not null | CURRENT_TIMESTAMP | Creation timestamp |
| updated_at | timestamp with time zone | not null | CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- Primary key:
secrets_metadata_pkeyonid - Unique constraint:
(tenant_id, secret_name)for secret uniqueness per tenant - Unique constraint:
infisical_secret_idfor Infisical integration - Index on
tenant_idfor tenant-scoped queries - Index on
statusfor lifecycle queries - Index on
environmentfor environment filtering - Index on
created_atfor timeline queries
Table: public.secret_access_logs
Purpose: Tracks every access to secrets for security auditing and compliance. Provides complete audit trail of all secret usage.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| id | uuid | not null | gen_random_uuid() | Primary key |
| secret_id | uuid | not null | Foreign key to secrets_metadata.id | |
| user_id | uuid | not null | User who accessed the secret | |
| tenant_id | uuid | not null | Tenant context for access isolation | |
| action | enum ('read', 'update', 'rotate', 'revoke', 'create', 'delete') | not null | Type of access/action | |
| reason | text | Why was the secret accessed (compliance context) | ||
| ip_address | character varying(45) | Source IP address of access | ||
| user_agent | character varying(500) | Browser/client information | ||
| status | enum ('success', 'failed') | not null | 'success' | Whether access was successful |
| error_message | text | Error details if access failed | ||
| accessed_at | timestamp with time zone | not null | CURRENT_TIMESTAMP | When the access occurred |
| created_at | timestamp with time zone | not null | CURRENT_TIMESTAMP | Log creation timestamp |
Indexes:
- Primary key:
secret_access_logs_pkeyonid - Index on
secret_idfor secret-specific access queries - Index on
user_idfor user activity tracking - Index on
tenant_idfor tenant-scoped access reports - Index on
accessed_atfor timeline analysis - Index on
actionfor action-specific reports - Index on
statusfor success/failure analysis
Foreign Key Constraints:
secret_id→secrets_metadata.id(ON DELETE CASCADE)
Table: public.secret_audit_trails
Purpose: Stores immutable audit history of all metadata changes to secrets. Provides forensic trail for compliance and security investigations.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| id | uuid | not null | gen_random_uuid() | Primary key |
| secret_id | uuid | not null | Foreign key to secrets_metadata.id | |
| tenant_id | uuid | not null | Tenant context for isolation | |
| changed_by_user_id | uuid | not null | User who made the change | |
| event_type | enum ('created', 'updated', 'rotated', 'revoked', 'restored', 'metadata_changed', 'status_changed') | not null | Type of change event | |
| description | text | not null | Human-readable description of change | |
| changes | json | not null | Detailed change: {field: 'status', old_value: 'active', new_value: 'revoked'} | |
| previous_state | json | Full snapshot of secret before change | ||
| current_state | json | Full snapshot of secret after change | ||
| ip_address | character varying(45) | Source IP of the change | ||
| user_agent | character varying(500) | Browser/client information | ||
| change_reason | text | Reason/justification for change | ||
| event_at | timestamp with time zone | not null | CURRENT_TIMESTAMP | When the change occurred |
| recorded_at | timestamp with time zone | not null | CURRENT_TIMESTAMP | When audit trail was recorded |
| is_sensitive_operation | boolean | not null | false | Flag for especially sensitive changes |
Indexes:
- Primary key:
secret_audit_trails_pkeyonid - Index on
secret_idfor change history of specific secrets - Index on
tenant_idfor tenant-scoped audit reports - Index on
event_typefor event-type filtering - Index on
event_atfor timeline queries - Index on
changed_by_user_idfor user activity tracking - Index on
is_sensitive_operationfor sensitive change detection
Foreign Key Constraints:
secret_id→secrets_metadata.id(ON DELETE CASCADE)
M58 Summary
The M58 migration introduces a comprehensive secrets management system with:
- Secrets Metadata: Central registry of all managed secrets with lifecycle tracking
- Access Logging: Complete audit trail of every secret access for compliance
- Audit Trails: Immutable change history for forensics and investigations
- Multi-tenancy: All tables scoped to
tenant_idfor data isolation - Infisical Integration: Foreign key mapping to Infisical vault via
infisical_secret_idandinfisical_workspace_id - Compliance Ready: Full audit trail, access logging, and change tracking for regulatory requirements