123

Tables (12)

animal_case

  • id — BIGINT — Primary key — NOT NULL
  • case_type — VARCHAR(20) — NOT NULL
  • source_type — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • is_matchable — BOOLEAN — NOT NULL
  • version — BIGINT — NOT NULL
  • name — VARCHAR(50)
  • species — VARCHAR(20) — NOT NULL
  • breed_name — VARCHAR(100)
  • sex — VARCHAR(20) — NOT NULL
  • color — VARCHAR(100)
  • event_date — DATE — NOT NULL
  • event_time — TIME
  • feature_text — TEXT
  • closed_at — TIMESTAMPTZ
  • deleted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

animal_case_location

  • animal_case_id — BIGINT — Foreign key → animal_case.id — NOT NULL
  • public_location — VARCHAR(255) — NOT NULL
  • exact_location_ciphertext — TEXT
  • latitude — NUMERIC(9, 6)
  • longitude — NUMERIC(9, 6)

animal_photo

  • id — BIGINT — Primary key — NOT NULL
  • animal_case_id — BIGINT — Foreign key → animal_case.id — NOT NULL
  • storage_type — VARCHAR(20) — NOT NULL
  • storage_uri — TEXT — NOT NULL
  • sort_order — SMALLINT — NOT NULL
  • checksum_sha256 — CHAR(64)
  • created_at — TIMESTAMPTZ — NOT NULL

auth_session

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • refresh_token_hash — CHAR(64) — NOT NULL
  • expires_at — TIMESTAMPTZ — NOT NULL
  • revoked_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • last_used_at — TIMESTAMPTZ

ingestion_run

  • id — BIGINT — Primary key — NOT NULL
  • source_system — VARCHAR(50) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • requested_from_date — DATE
  • requested_to_date — DATE
  • fetched_count — INTEGER — NOT NULL
  • inserted_count — INTEGER — NOT NULL
  • updated_count — INTEGER — NOT NULL
  • failed_count — INTEGER — NOT NULL
  • last_source_updated_at — TIMESTAMPTZ
  • error_summary — TEXT
  • started_at — TIMESTAMPTZ — NOT NULL
  • completed_at — TIMESTAMPTZ

match_candidate

  • id — BIGINT — Primary key — NOT NULL
  • match_run_id — BIGINT — Foreign key → match_run.id — NOT NULL
  • target_case_id — BIGINT — Foreign key → animal_case.id — NOT NULL
  • rank — INTEGER — NOT NULL
  • total_score — DOUBLE PRECISION — NOT NULL
  • image_score — DOUBLE PRECISION
  • distance_km — DOUBLE PRECISION
  • time_gap_days — INTEGER
  • created_at — TIMESTAMPTZ — NOT NULL

match_run

  • id — BIGINT — Primary key — NOT NULL
  • query_case_id — BIGINT — Foreign key → animal_case.id — NOT NULL
  • run_type — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • model_id — VARCHAR(100) — NOT NULL
  • model_version — VARCHAR(50) — NOT NULL
  • candidate_count — INTEGER — NOT NULL
  • error_code — VARCHAR(100)
  • started_at — TIMESTAMPTZ
  • completed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

member

  • id — BIGINT — Primary key — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • nickname — VARCHAR(30) — NOT NULL
  • phone_ciphertext — TEXT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ

member_consent

  • id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • consent_type — VARCHAR(30) — NOT NULL
  • policy_version — VARCHAR(20) — NOT NULL
  • agreed — BOOLEAN — NOT NULL
  • decided_at — TIMESTAMPTZ — NOT NULL
  • revoked_at — TIMESTAMPTZ

shelter

  • id — BIGINT — Primary key — NOT NULL
  • care_reg_no — VARCHAR(50) — NOT NULL
  • name — VARCHAR(150) — NOT NULL
  • phone — VARCHAR(50)
  • address — TEXT
  • jurisdiction — VARCHAR(150)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

shelter_animal

  • animal_case_id — BIGINT — Foreign key → animal_case.id — NOT NULL
  • desertion_no — VARCHAR(50) — NOT NULL
  • shelter_id — BIGINT — Foreign key → shelter.id — NOT NULL
  • ingestion_run_id — BIGINT — Foreign key → ingestion_run.id
  • notice_no — VARCHAR(100)
  • notice_start_date — DATE
  • notice_end_date — DATE
  • process_state_raw — VARCHAR(100)
  • end_reason_raw — VARCHAR(255)
  • age_text — VARCHAR(100)
  • weight_text — VARCHAR(100)
  • neuter_status — VARCHAR(20) — NOT NULL
  • source_updated_at — TIMESTAMPTZ
  • last_synced_at — TIMESTAMPTZ — NOT NULL

user_post

  • animal_case_id — BIGINT — Foreign key → animal_case.id — NOT NULL
  • member_id — BIGINT — Foreign key → member.id — NOT NULL
  • contact_visible — BOOLEAN — NOT NULL
  • exact_location_visible — BOOLEAN — NOT NULL
  • disclosure_policy_version — VARCHAR(20) — NOT NULL
  • disclosure_consented_at — TIMESTAMPTZ — NOT NULL
  • close_reason — VARCHAR(30)