ERD

Tables (30)

app_session

  • session_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • user_id — UUID — Foreign key → user_account.user_id — NOT NULL
  • created_at — TIMESTAMP
  • expires_at — TIMESTAMP
  • revoked_at — TIMESTAMP

approval_log

  • approval_log_id — UUID
  • actor_user_id — UUID — Foreign key → user_account.user_id — NOT NULL
  • fact_candidate_id — UUID — Foreign key → fact_candidate.fact_candidate_id
  • kpi_fact_id — UUID — Foreign key → kpi_fact.kpi_fact_id
  • report_section_draft_id — UUID — Foreign key → report_section_draft.report_section_draft_id
  • action — VARCHAR(100)
  • action_note — TEXT
  • created_at — TIMESTAMP

approval_scope

  • approval_scope_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • user_id — UUID — Foreign key → user_account.user_id — NOT NULL
  • scope_type — VARCHAR(100)
  • scope_value — VARCHAR(255)
  • action_type — VARCHAR(100)
  • is_active — BOOLEAN

audit_log

  • audit_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • actor_user_id — UUID — Foreign key → user_account.user_id — NOT NULL
  • event_type — VARCHAR(100)
  • target_type — VARCHAR(100)
  • target_id — UUID
  • old_value — TEXT
  • new_value — TEXT
  • request_id — UUID
  • created_at — TIMESTAMP

company

  • company_id — UUID
  • tenant_id — UUID — Foreign key → tenant.tenant_id — NOT NULL
  • company_name — VARCHAR(255)
  • industry_code — VARCHAR(100)

department

  • department_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • department_name — VARCHAR(255)

dictionary_rule

  • dictionary_rule_id — UUID
  • metric_id — VARCHAR(100) — Foreign key → metric_master.metric_id — NOT NULL
  • rule_type — VARCHAR(100)
  • keywords — TEXT
  • regex_pattern — TEXT
  • is_active — BOOLEAN

evidence_chunk

  • evidence_chunk_id — UUID
  • source_document_version_id — UUID — Foreign key → source_document_version.source_document_version_id — NOT NULL
  • chunk_text — TEXT
  • source_page — INT
  • chunk_order — INT

evidence_embedding

  • evidence_embedding_id — UUID
  • evidence_chunk_id — UUID — Foreign key → evidence_chunk.evidence_chunk_id — NOT NULL
  • embedding_model — VARCHAR(255)
  • vector_data — TEXT
  • created_at — TIMESTAMP

evidence_type_master

  • evidence_type_id — VARCHAR(100)
  • evidence_type_name — VARCHAR(255)
  • description — TEXT

extraction_job

  • extraction_job_id — UUID
  • source_document_version_id — UUID — Foreign key → source_document_version.source_document_version_id — NOT NULL
  • job_status — VARCHAR(50)
  • started_at — TIMESTAMP
  • ended_at — TIMESTAMP

fact_candidate

  • fact_candidate_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • metric_id — VARCHAR(100) — Foreign key → metric_master.metric_id — NOT NULL
  • evidence_chunk_id — UUID — Foreign key → evidence_chunk.evidence_chunk_id
  • extraction_job_id — UUID — Foreign key → extraction_job.extraction_job_id
  • issue_group_code — VARCHAR(100)
  • value_numeric — DECIMAL(18,4)
  • value_text — VARCHAR(255)
  • unit — VARCHAR(100)
  • status — VARCHAR(50)
  • created_at — TIMESTAMP

industry_profile

  • industry_profile_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • industry_code — VARCHAR(100)
  • industry_name — VARCHAR(255)
  • profile_version — VARCHAR(100)

issue_master

  • issue_id — VARCHAR(100)
  • issue_name — VARCHAR(255)
  • issue_group_code — VARCHAR(100)
  • description — TEXT

kpi_fact

  • kpi_fact_id — UUID
  • fact_candidate_id — UUID — Foreign key → fact_candidate.fact_candidate_id
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • metric_id — VARCHAR(100) — Foreign key → metric_master.metric_id — NOT NULL
  • value_numeric — DECIMAL(18,4)
  • value_text — VARCHAR(255)
  • unit — VARCHAR(100)
  • version_no — INT
  • is_latest — BOOLEAN
  • created_at — TIMESTAMP

metric_master

  • metric_id — VARCHAR(100)
  • issue_id — VARCHAR(100) — Foreign key → issue_master.issue_id — NOT NULL
  • evidence_type_id — VARCHAR(100) — Foreign key → evidence_type_master.evidence_type_id
  • metric_name — VARCHAR(255)
  • unit — VARCHAR(100)
  • calculation_logic — TEXT

model_registry

  • model_id — UUID
  • model_name — VARCHAR(255)
  • model_vendor — VARCHAR(255)
  • model_version — VARCHAR(100)
  • is_active — BOOLEAN

narrative_reference

  • narrative_reference_id — UUID
  • report_section_draft_id — UUID — Foreign key → report_section_draft.report_section_draft_id — NOT NULL
  • kpi_fact_id — UUID — Foreign key → kpi_fact.kpi_fact_id
  • evidence_chunk_id — UUID — Foreign key → evidence_chunk.evidence_chunk_id
  • reference_type — VARCHAR(50)
  • created_at — TIMESTAMP

presigned_url_log

  • presigned_url_log_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • issued_by_user_id — UUID — Foreign key → user_account.user_id
  • s3_bucket — VARCHAR(255)
  • s3_key — VARCHAR(500)
  • http_method — VARCHAR(20)
  • expires_at — TIMESTAMP
  • created_at — TIMESTAMP

prompt_template

  • prompt_template_id — UUID
  • template_name — VARCHAR(255)
  • template_type — VARCHAR(100)
  • description — TEXT

prompt_template_version

  • prompt_template_version_id — UUID
  • prompt_template_id — UUID — Foreign key → prompt_template.prompt_template_id — NOT NULL
  • version_no — INT
  • prompt_text — TEXT
  • created_at — TIMESTAMP

report_run

  • report_run_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • model_id — UUID — Foreign key → model_registry.model_id — NOT NULL
  • prompt_template_version_id — UUID — Foreign key → prompt_template_version.prompt_template_version_id — NOT NULL
  • score_version_id — UUID — Foreign key → score_version.score_version_id — NOT NULL
  • run_status — VARCHAR(50)
  • created_at — TIMESTAMP

report_section_draft

  • report_section_draft_id — UUID
  • report_run_id — UUID — Foreign key → report_run.report_run_id — NOT NULL
  • section_code — VARCHAR(100)
  • section_title — VARCHAR(255)
  • draft_text — TEXT
  • status — VARCHAR(50)
  • created_at — TIMESTAMP

score_component

  • score_component_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • issue_id — VARCHAR(100) — Foreign key → issue_master.issue_id — NOT NULL
  • score_version_id — UUID — Foreign key → score_version.score_version_id — NOT NULL
  • evidence_chunk_id — UUID — Foreign key → evidence_chunk.evidence_chunk_id
  • kpi_fact_id — UUID — Foreign key → kpi_fact.kpi_fact_id
  • axis — VARCHAR(50)
  • component_type — VARCHAR(100)
  • score_value — DECIMAL(10,4)
  • created_at — TIMESTAMP

score_version

  • score_version_id — UUID
  • version_name — VARCHAR(255)
  • version_no — INT
  • description — TEXT
  • created_at — TIMESTAMP

source_document

  • source_document_id — UUID
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • document_name — VARCHAR(255)
  • document_type — VARCHAR(100)
  • uploaded_by_user_id — UUID — Foreign key → user_account.user_id
  • created_at — TIMESTAMP

source_document_version

  • source_document_version_id — UUID
  • source_document_id — UUID — Foreign key → source_document.source_document_id — NOT NULL
  • version_no — INT
  • file_url — VARCHAR(500)
  • checksum — VARCHAR(255)
  • created_at — TIMESTAMP

tenant

  • tenant_id — UUID
  • tenant_name — VARCHAR(255)
  • status — VARCHAR(50)

user_account

  • user_id — UUID
  • tenant_id — UUID — Foreign key → tenant.tenant_id — NOT NULL
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • department_id — UUID — Foreign key → department.department_id
  • email — VARCHAR(255)
  • auth_subject — VARCHAR(255)
  • role_code — VARCHAR(100)
  • status — VARCHAR(50)

user_invite

  • user_invite_id — UUID
  • inviter_user_id — UUID — Foreign key → user_account.user_id — NOT NULL
  • company_id — UUID — Foreign key → company.company_id — NOT NULL
  • invite_email — VARCHAR(255)
  • invite_token — VARCHAR(255)
  • status — VARCHAR(50)
  • created_at — TIMESTAMP
  • expires_at — TIMESTAMP