witan

Tables (20)

amending_acts

  • id — BIGINT — Primary key — NOT NULL
  • jurisdiction_id — BIGINT — Foreign key → jurisdictions.id — NOT NULL
  • act_code — VARCHAR(40) — NOT NULL
  • act_year — SMALLINT
  • act_number — INT
  • title — TEXT
  • oj_url — TEXT
  • published_on — DATE
  • in_force_on — DATE
  • applies_from — DATE
  • verified_by — VARCHAR(100)
  • verified_at — TIMESTAMPTZ

function_codes

  • id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(60) — NOT NULL
  • name_en — TEXT — NOT NULL
  • name_ko — TEXT

ingredient_functions

  • id — BIGINT — Primary key — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • function_code_id — BIGINT — Foreign key → function_codes.id — NOT NULL
  • source_record_id — BIGINT — Foreign key → source_records.id — NOT NULL

ingredient_group_members

  • id — BIGINT — Primary key — NOT NULL
  • substance_group_id — BIGINT — Foreign key → substance_groups.id — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • reviewer — VARCHAR(100)
  • reviewed_at — TIMESTAMPTZ

ingredient_histories

  • id — BIGINT — Primary key — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • field — VARCHAR(60) — NOT NULL
  • old_value — TEXT
  • new_value — TEXT
  • source_snapshot_id — BIGINT — Foreign key → source_snapshots.id
  • changed_by — VARCHAR(100)
  • changed_at — TIMESTAMPTZ — NOT NULL

ingredient_identifiers

  • id — BIGINT — Primary key — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • id_type — VARCHAR(20) — NOT NULL
  • value — VARCHAR(100) — NOT NULL
  • checksum_ok — BOOLEAN
  • is_generic — BOOLEAN — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • source_record_id — BIGINT — Foreign key → source_records.id

ingredient_names

  • id — BIGINT — Primary key — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • name — TEXT — NOT NULL
  • name_norm — TEXT — NOT NULL
  • lang — VARCHAR(8) — NOT NULL
  • name_type — VARCHAR(20) — NOT NULL
  • jurisdiction_id — BIGINT — Foreign key → jurisdictions.id
  • is_primary — BOOLEAN — NOT NULL
  • verified — BOOLEAN — NOT NULL
  • source_record_id — BIGINT — Foreign key → source_records.id

ingredient_regulation_links

  • id — BIGINT — Primary key — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • regulation_entry_key — VARCHAR(80) — NOT NULL
  • matched_key — VARCHAR(20) — NOT NULL
  • match_method — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • reviewer — VARCHAR(100)
  • reviewed_at — TIMESTAMPTZ
  • note — TEXT

ingredient_source_links

  • id — BIGINT — Primary key — NOT NULL
  • ingredient_id — BIGINT — Foreign key → ingredients.id — NOT NULL
  • source_record_id — BIGINT — Foreign key → source_records.id — NOT NULL
  • match_key — VARCHAR(20) — NOT NULL
  • match_method — VARCHAR(20) — NOT NULL
  • candidate_count — SMALLINT — NOT NULL
  • score — NUMERIC(5,4)
  • status — VARCHAR(20) — NOT NULL
  • reviewer — VARCHAR(100)
  • reviewed_at — TIMESTAMPTZ
  • note — TEXT

ingredients

  • id — BIGINT — Primary key — NOT NULL
  • kor_std_name — TEXT — NOT NULL
  • inci_name — TEXT
  • inci_norm — TEXT
  • origin_desc — TEXT
  • status — VARCHAR(20) — NOT NULL
  • parent_ingredient_id — BIGINT — Foreign key → ingredients.id
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

jurisdictions

  • id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(10) — NOT NULL
  • name_ko — TEXT — NOT NULL
  • parent_jurisdiction_id — BIGINT — Foreign key → jurisdictions.id

regulation_conditions

  • id — BIGINT — Primary key — NOT NULL
  • regulation_entry_id — BIGINT — Foreign key → regulation_entries.id — NOT NULL
  • clause — VARCHAR(10)
  • product_type — TEXT
  • body_part — TEXT
  • max_value — NUMERIC(12,6)
  • max_unit — VARCHAR(20)
  • max_basis — TEXT
  • max_raw — TEXT
  • other_text — TEXT
  • warning_text — TEXT
  • label_threshold_leave_on — NUMERIC(12,6)
  • label_threshold_rinse_off — NUMERIC(12,6)
  • market_ban_from — DATE
  • sale_ban_from — DATE

regulation_entries

  • id — BIGINT — Primary key — NOT NULL
  • regulation_list_id — BIGINT — Foreign key → regulation_lists.id — NOT NULL
  • source_snapshot_id — BIGINT — Foreign key → source_snapshots.id — NOT NULL
  • source_record_id — BIGINT — Foreign key → source_records.id
  • entry_key — VARCHAR(80) — NOT NULL
  • ref_no — VARCHAR(20)
  • row_seq — SMALLINT — NOT NULL
  • substance_name — TEXT — NOT NULL
  • amending_act_id — BIGINT — Foreign key → amending_acts.id
  • substance_group_id — BIGINT — Foreign key → substance_groups.id
  • source_updated_on — DATE
  • cmr — TEXT
  • is_active — BOOLEAN — NOT NULL
  • is_secondary — BOOLEAN — NOT NULL

regulation_entry_identifiers

  • id — BIGINT — Primary key — NOT NULL
  • regulation_entry_id — BIGINT — Foreign key → regulation_entries.id — NOT NULL
  • id_type — VARCHAR(20) — NOT NULL
  • value — TEXT — NOT NULL
  • value_norm — TEXT — NOT NULL
  • position — SMALLINT
  • checksum_ok — BOOLEAN

regulation_entry_xrefs

  • id — BIGINT — Primary key — NOT NULL
  • regulation_entry_id — BIGINT — Foreign key → regulation_entries.id — NOT NULL
  • primary_entry_key — VARCHAR(80) — NOT NULL
  • match_method — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • reviewer — VARCHAR(100)
  • reviewed_at — TIMESTAMPTZ
  • note — TEXT

regulation_lists

  • id — BIGINT — Primary key — NOT NULL
  • jurisdiction_id — BIGINT — Foreign key → jurisdictions.id — NOT NULL
  • code — VARCHAR(40) — NOT NULL
  • name — TEXT — NOT NULL
  • list_type — VARCHAR(20) — NOT NULL
  • function_scope — VARCHAR(20)
  • legal_basis — TEXT

source_records

  • id — BIGINT — Primary key — NOT NULL
  • source_snapshot_id — BIGINT — Foreign key → source_snapshots.id — NOT NULL
  • external_key — TEXT
  • raw — JSONB — NOT NULL
  • is_active — BOOLEAN — NOT NULL

source_snapshots

  • id — BIGINT — Primary key — NOT NULL
  • source_id — BIGINT — Foreign key → sources.id — NOT NULL
  • target — VARCHAR(40)
  • collected_at — TIMESTAMPTZ — NOT NULL
  • source_updated_on — DATE
  • total_count — INT
  • row_count — INT
  • file_path — TEXT
  • sha256 — CHAR(64)
  • parse_status — VARCHAR(20) — NOT NULL

sources

  • id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(40) — NOT NULL
  • name — TEXT — NOT NULL
  • jurisdiction_id — BIGINT — Foreign key → jurisdictions.id
  • is_official — BOOLEAN — NOT NULL
  • is_legal — BOOLEAN — NOT NULL
  • is_primary — BOOLEAN — NOT NULL
  • priority — SMALLINT — NOT NULL
  • url — TEXT
  • license_note — TEXT

substance_groups

  • id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(60) — NOT NULL
  • name — TEXT — NOT NULL
  • note — TEXT