i-itda

Tables (17)

chat_log_sources

  • chat_log_id — VARCHAR(20) — Foreign key → chat_logs.chat_log_id — NOT NULL
  • child_context_id — VARCHAR(20) — Foreign key → child_context.child_context_id — NOT NULL

chat_logs

  • chat_log_id — VARCHAR(20) — Primary key — NOT NULL
  • child_id — VARCHAR(20) — Foreign key → children.child_id
  • asked_by — VARCHAR(20) — Foreign key → users.user_id
  • question — TEXT
  • answer — TEXT
  • answerable — BOOLEAN
  • asked_at — TIMESTAMPTZ

child_context

  • child_context_id — VARCHAR(20) — Primary key — NOT NULL
  • child_id — VARCHAR(20) — Foreign key → children.child_id
  • summary_id — VARCHAR(20) — Foreign key → summaries.summary_id
  • created_at — TIMESTAMPTZ

children

  • child_id — VARCHAR(20) — Primary key — NOT NULL
  • name — VARCHAR(50)
  • birth_date — DATE
  • created_at — TIMESTAMPTZ

consent_scopes

  • consent_scope_id — VARCHAR(20) — Primary key — NOT NULL
  • child_id — VARCHAR(20) — Foreign key → children.child_id
  • institution_id — VARCHAR(20) — Foreign key → institutions.institution_id
  • allowed_fields — VARCHAR(255)
  • status — VARCHAR(20)
  • granted_by — VARCHAR(20) — Foreign key → users.user_id
  • granted_at — TIMESTAMPTZ

gate1_approvals

  • gate1_approval_id — VARCHAR(20) — Primary key — NOT NULL
  • summary_id — VARCHAR(20) — Foreign key → summaries.summary_id
  • decision — VARCHAR(10)
  • edited_content — TEXT
  • approved_by — VARCHAR(20) — Foreign key → users.user_id
  • approved_at — TIMESTAMPTZ

gate2_approvals

  • gate2_approval_id — VARCHAR(20) — Primary key — NOT NULL
  • insight_id — VARCHAR(20) — Foreign key → insights.insight_id
  • decision — VARCHAR(10)
  • approved_by — VARCHAR(20) — Foreign key → users.user_id
  • approved_at — TIMESTAMPTZ

insight_target_institutions

  • insight_id — VARCHAR(20) — Foreign key → insights.insight_id — NOT NULL
  • institution_id — VARCHAR(20) — Foreign key → institutions.institution_id — NOT NULL

insights

  • insight_id — VARCHAR(20) — Primary key — NOT NULL
  • child_id — VARCHAR(20) — Foreign key → children.child_id
  • period — VARCHAR(20)
  • content — TEXT
  • gate2_status — VARCHAR(10)
  • created_at — TIMESTAMPTZ

institutions

  • institution_id — VARCHAR(20) — Primary key — NOT NULL
  • name — VARCHAR(100)
  • type — VARCHAR(20)
  • created_at — TIMESTAMPTZ

matching_result_children

  • matching_result_id — VARCHAR(20) — Foreign key → matching_results.matching_result_id — NOT NULL
  • child_id — VARCHAR(20) — Foreign key → children.child_id — NOT NULL

matching_results

  • matching_result_id — VARCHAR(20) — Primary key — NOT NULL
  • raw_record_ref_id — VARCHAR(20) — Foreign key → raw_records.raw_record_ref_id
  • child_id — VARCHAR(20) — Foreign key → children.child_id
  • status — VARCHAR(20)
  • confidence_score — DECIMAL
  • multi_child_detected — BOOLEAN
  • matched_at — TIMESTAMPTZ

raw_records

  • raw_record_ref_id — VARCHAR(20) — Primary key — NOT NULL
  • institution_id — VARCHAR(20) — Foreign key → institutions.institution_id
  • type — VARCHAR(20)
  • local_path — VARCHAR(255)
  • captured_at — TIMESTAMPTZ
  • match_status — VARCHAR(20)
  • created_at — TIMESTAMPTZ

sharing_logs

  • sharing_log_id — VARCHAR(20) — Primary key — NOT NULL
  • insight_id — VARCHAR(20) — Foreign key → insights.insight_id
  • institution_id — VARCHAR(20) — Foreign key → institutions.institution_id
  • status — VARCHAR(20)
  • dispatched_at — TIMESTAMPTZ

summaries

  • summary_id — VARCHAR(20) — Primary key — NOT NULL
  • child_id — VARCHAR(20) — Foreign key → children.child_id
  • validation_result_id — VARCHAR(20) — Foreign key → validation_results.validation_result_id
  • summary_date — DATE
  • content — TEXT
  • gate1_status — VARCHAR(10)
  • created_at — TIMESTAMPTZ

users

  • user_id — VARCHAR(20) — Primary key — NOT NULL
  • role — VARCHAR(20)
  • institution_id — VARCHAR(20) — Foreign key → institutions.institution_id
  • name — VARCHAR(50)
  • email — VARCHAR(100)
  • created_at — TIMESTAMPTZ

validation_results

  • validation_result_id — VARCHAR(20) — Primary key — NOT NULL
  • matching_result_id — VARCHAR(20) — Foreign key → matching_results.matching_result_id
  • status — VARCHAR(10)
  • violation_reason — VARCHAR(100)
  • checked_at — TIMESTAMPTZ