"""Table definitions (data dictionary Part B) and engine factory. Single source of truth for both backends. `python -m api.cli export-ddl` writes the Postgres migration from this metadata. Lists (`text[]` in Part B) are JSON here so SQLite and Postgres behave identically. """ from __future__ import annotations import uuid from datetime import datetime, timezone from functools import lru_cache from sqlalchemy import (JSON, Boolean, CheckConstraint, Column, Date, DateTime, Float, ForeignKey, Integer, MetaData, Numeric, String, Table, Text, TypeDecorator, create_engine, event) from sqlalchemy.engine import Engine from . import constants as C from .config import get_settings class UTCDateTime(TypeDecorator): """timestamptz on Postgres; stores UTC and returns aware datetimes on SQLite.""" impl = DateTime(timezone=True) cache_ok = True def process_bind_param(self, value, dialect): if value is None: return None if value.tzinfo is None: value = value.replace(tzinfo=timezone.utc) return value.astimezone(timezone.utc) def process_result_value(self, value, dialect): if value is not None and value.tzinfo is None: return value.replace(tzinfo=timezone.utc) return value def now() -> datetime: return datetime.now(timezone.utc) def new_id() -> str: return str(uuid.uuid4()) def _in(col: str, values) -> CheckConstraint: quoted = ",".join(f"'{v}'" for v in values) return CheckConstraint(f"{col} IN ({quoted})", name=f"ck_{col}_values") metadata = MetaData() TS = UTCDateTime() def created(): return Column("created_at", TS, nullable=False, default=now) sources = Table( "sources", metadata, Column("source_key", String, primary_key=True), Column("name", String, nullable=False), Column("source_type", String, nullable=False, default="news_outlet"), Column("base_url", String, nullable=False), Column("feeds", JSON, nullable=False, default=list), Column("crawl_delay_seconds", Integer, nullable=False, default=10), Column("robots_checked_at", TS), Column("robots_summary", Text), Column("tos_url", String), Column("tos_checked_at", TS), Column("tos_result", String, nullable=False, default="unchecked"), Column("enabled", Boolean, nullable=False, default=False), Column("reliability_rating", Integer), Column("notes", Text), created(), _in("source_type", C.SOURCE_TYPE), _in("tos_result", C.TOS_RESULT), CheckConstraint( "NOT enabled OR (tos_result = 'no_explicit_ban' AND tos_checked_at IS NOT NULL " "AND robots_checked_at IS NOT NULL)", name="ck_sources_enabled_requires_checks"), ) collection_runs = Table( "collection_runs", metadata, Column("run_id", String, primary_key=True, default=new_id), Column("source_key", String, ForeignKey("sources.source_key"), nullable=False), Column("batch_label", String, nullable=False), Column("trigger", String, nullable=False), Column("started_at", TS, nullable=False, default=now), Column("finished_at", TS), Column("status", String, nullable=False, default="running"), Column("feed_items", Integer, nullable=False, default=0), Column("skipped_seen", Integer, nullable=False, default=0), Column("candidates", Integer, nullable=False, default=0), Column("low_confidence", Integer, nullable=False, default=0), Column("fetch_errors", Integer, nullable=False, default=0), Column("error_message", Text), created(), _in("trigger", C.RUN_TRIGGER), _in("status", C.RUN_STATUS), ) # What a run is doing, line by line, so the admin console can follow it live. Never holds article text; # an article that was discarded is not named here either (only its host and score). run_events = Table( "run_events", metadata, Column("event_id", Integer, primary_key=True, autoincrement=True), Column("run_id", String, ForeignKey("collection_runs.run_id", ondelete="CASCADE"), nullable=False, index=True), Column("at", TS, nullable=False, default=now), Column("kind", String, nullable=False), # info | feed | kept | discarded | failed | duplicate | error | done Column("message", Text, nullable=False), ) raw_reports = Table( "raw_reports", metadata, Column("article_id", String, primary_key=True), Column("source_key", String, ForeignKey("sources.source_key"), nullable=False), Column("run_id", String, ForeignKey("collection_runs.run_id")), Column("source_url", String, nullable=False, unique=True), Column("title", Text, nullable=False), Column("published_at", TS), Column("published_at_raw", String), Column("fetched_at", TS, nullable=False, default=now), Column("raw_text", Text), Column("raw_text_purged_at", TS), Column("collection_mode", String, nullable=False, default="rss"), Column("disposition", String, nullable=False), Column("disposition_reason", Text), Column("is_candidate", Boolean, nullable=False, default=False), Column("matched_keywords", JSON, nullable=False, default=list), Column("corroborating", JSON, nullable=False, default=list), Column("relevance_pct", Integer), Column("schema_version", String, nullable=False), created(), _in("collection_mode", C.COLLECTION_MODE), _in("disposition", C.DISPOSITION), ) categories = Table( "categories", metadata, Column("category_id", Integer, primary_key=True, autoincrement=True), Column("code", String, nullable=False, unique=True), Column("label", String, nullable=False), Column("description", Text), Column("active", Boolean, nullable=False, default=True), created(), ) ref_states = Table( "ref_states", metadata, Column("state_code", String, primary_key=True), Column("name", String, nullable=False, unique=True), Column("geopolitical_zone", String, nullable=False), Column("centroid_lat", Numeric(9, 6), nullable=False), Column("centroid_lng", Numeric(9, 6), nullable=False), ) ref_lgas = Table( "ref_lgas", metadata, Column("lga_id", Integer, primary_key=True, autoincrement=True), Column("state_code", String, ForeignKey("ref_states.state_code"), nullable=False), Column("name", String, nullable=False), Column("centroid_lat", Numeric(9, 6)), Column("centroid_lng", Numeric(9, 6)), ) ref_places = Table( "ref_places", metadata, Column("place_id", Integer, primary_key=True, autoincrement=True), Column("lga_id", Integer, ForeignKey("ref_lgas.lga_id"), nullable=False), Column("name", String, nullable=False), Column("place_kind", String, nullable=False, default="town"), Column("lat", Numeric(9, 6)), Column("lng", Numeric(9, 6)), Column("aliases", JSON, nullable=False, default=list), _in("place_kind", C.PLACE_KIND), ) locations = Table( "locations", metadata, Column("location_id", String, primary_key=True, default=new_id), Column("state_code", String, ForeignKey("ref_states.state_code"), nullable=False), Column("lga_id", Integer, ForeignKey("ref_lgas.lga_id")), Column("place_id", Integer, ForeignKey("ref_places.place_id")), Column("place_text", String), Column("latitude", Numeric(9, 6)), Column("longitude", Numeric(9, 6)), Column("precision", String, nullable=False), Column("is_generalised", Boolean, nullable=False, default=False), Column("public_latitude", Numeric(9, 6)), Column("public_longitude", Numeric(9, 6)), Column("public_precision", String, nullable=False), Column("confirmed_by", String, nullable=False), created(), _in("precision", C.LOCATION_PRECISION), _in("public_precision", C.LOCATION_PRECISION), ) incidents = Table( "incidents", metadata, Column("incident_id", String, primary_key=True, default=new_id), Column("public_ref", String, nullable=False, unique=True), Column("title", Text, nullable=False), Column("summary", Text, nullable=False), Column("category_id", Integer, ForeignKey("categories.category_id"), nullable=False), Column("event_date", Date, nullable=False), Column("severity", String), Column("location_id", String, ForeignKey("locations.location_id"), nullable=False), Column("casualty_status", String, nullable=False, default="unknown"), Column("casualty_summary", Text), Column("killed", Integer), Column("injured", Integer), Column("abducted_or_missing", Integer), Column("public_actor_label", String), Column("verification_status", String, nullable=False, default="pending"), Column("visibility", String, nullable=False, default="draft"), Column("confidence", String, nullable=False, default="low"), Column("version", Integer, nullable=False, default=1), Column("published_at", TS), Column("published_by", String), Column("updated_at", TS, nullable=False, default=now), created(), _in("severity", C.SEVERITY_LEVEL), _in("casualty_status", C.CASUALTY_STATUS), _in("verification_status", C.VERIFICATION_STATUS), _in("visibility", C.VISIBILITY), _in("confidence", C.CONFIDENCE_LEVEL), ) candidate_incidents = Table( "candidate_incidents", metadata, Column("candidate_id", String, primary_key=True, default=new_id), Column("article_id", String, ForeignKey("raw_reports.article_id"), nullable=False, unique=True), Column("extractor_version", String, nullable=False), Column("event_type_raw", String), Column("event_types_all", JSON, nullable=False, default=list), Column("suggested_category_id", Integer, ForeignKey("categories.category_id")), Column("event_date", Date), Column("event_date_source", String, nullable=False, default="published"), Column("location_text", String), Column("locations_all", JSON, nullable=False, default=list), Column("suggested_state_code", String, ForeignKey("ref_states.state_code")), Column("suggested_lga_id", Integer, ForeignKey("ref_lgas.lga_id")), Column("actors_raw", JSON, nullable=False, default=list), Column("impact_text", String), Column("casualty_count_hint", Integer), Column("extraction_confidence", String, nullable=False, default="low"), Column("status", String, nullable=False, default="new"), Column("incident_id", String, ForeignKey("incidents.incident_id")), Column("review_note", Text), Column("similar", JSON, nullable=False, default=list, server_default="[]"), Column("duplicate_score", Integer), # set when a person kept a longer report of the same event and discarded this one Column("duplicate_of", String, ForeignKey("candidate_incidents.candidate_id")), # ids (candidates or incidents) a person said are NOT the same event, so they are never flagged again Column("dup_dismissed", JSON, nullable=False, default=list, server_default="[]"), created(), _in("event_date_source", C.DATE_SOURCE), _in("extraction_confidence", C.CONFIDENCE_LEVEL), _in("status", C.CANDIDATE_STATUS), ) # URL fingerprints of articles we read and discarded as below the relevance floor. No title, no text, # no outlet content: just enough to avoid fetching the same article again on the next run. seen_urls = Table( "seen_urls", metadata, Column("article_id", String, primary_key=True), Column("source_key", String, ForeignKey("sources.source_key"), nullable=False), Column("seen_at", TS, nullable=False, default=now), ) incident_sources = Table( "incident_sources", metadata, Column("incident_id", String, ForeignKey("incidents.incident_id"), primary_key=True), Column("article_id", String, ForeignKey("raw_reports.article_id"), primary_key=True), Column("role", String, nullable=False, default="primary"), Column("added_by", String, nullable=False), Column("added_at", TS, nullable=False, default=now), _in("role", C.SOURCE_ROLE), ) reviews = Table( "reviews", metadata, Column("review_id", String, primary_key=True, default=new_id), Column("subject_type", String, nullable=False), Column("subject_id", String, nullable=False), Column("reviewer_id", String, nullable=False), Column("decision", String, nullable=False), Column("reason", Text), Column("notes", Text), created(), _in("subject_type", C.REVIEW_SUBJECT), _in("decision", C.REVIEW_DECISION), CheckConstraint( "decision NOT IN ('reject','merge','restrict','unpublish') " "OR (reason IS NOT NULL AND length(trim(reason)) > 0)", name="ck_reviews_reason_required"), ) edits = Table( "edits", metadata, Column("edit_id", String, primary_key=True, default=new_id), Column("incident_id", String, ForeignKey("incidents.incident_id"), nullable=False), Column("field_name", String, nullable=False), Column("old_value", JSON), Column("new_value", JSON), Column("editor_id", String, nullable=False), Column("reason", Text, nullable=False), created(), CheckConstraint("length(trim(reason)) > 0", name="ck_edits_reason_required"), ) user_roles = Table( "user_roles", metadata, Column("user_id", String, primary_key=True), Column("role", String, nullable=False), Column("display_name", String, nullable=False), Column("active", Boolean, nullable=False, default=True), created(), _in("role", C.STAFF_ROLE), ) corrections = Table( "corrections", metadata, Column("correction_id", String, primary_key=True, default=new_id), Column("incident_id", String, ForeignKey("incidents.incident_id"), nullable=False), Column("message", Text, nullable=False), Column("status", String, nullable=False, default="open"), Column("handled_by", String), Column("handled_at", TS), created(), _in("status", C.CORRECTION_STATUS), CheckConstraint(f"length(message) <= {C.MAX_CORRECTION_LENGTH}", name="ck_corrections_len"), ) reporters = Table( # Profile (name, date of birth, phone, location, email) is held here so staff can identify a # reporter under the operating rules. Staff screens show only `reporter_id`; Reveal is logged. "reporters", metadata, Column("reporter_id", String, primary_key=True, default=new_id), # opaque id shown to staff Column("firebase_uid", String, nullable=False, unique=True), Column("status", String, nullable=False, default="active"), Column("accepted_count", Integer, nullable=False, default=0), Column("rejected_count", Integer, nullable=False, default=0), Column("terms_version", String), Column("privacy_version", String), Column("accepted_terms_at", TS), Column("last_seen_at", TS), Column("appeal_text", Text), # a suspended reporter asking a person to review (NDPA s.37) Column("appealed_at", TS), Column("full_name", String), Column("date_of_birth", Date), Column("phone", String), Column("state_code", String), Column("town", String), Column("email", String), Column("sign_in_provider", String), Column("profile_completed_at", TS), created(), _in("status", C.REPORTER_STATUS), ) retention_log = Table( # What was deleted, when and why: counts only, never the deleted content. "retention_log", metadata, Column("log_id", String, primary_key=True, default=new_id), Column("what", String, nullable=False), Column("count", Integer, nullable=False), Column("reason", String, nullable=False), created(), ) user_reports = Table( "user_reports", metadata, Column("report_id", String, primary_key=True, default=new_id), Column("reporter_id", String, ForeignKey("reporters.reporter_id"), nullable=False), Column("category_code", String, nullable=False), Column("state_code", String, ForeignKey("ref_states.state_code"), nullable=False), Column("event_date", Date, nullable=False), Column("description", Text, nullable=False), # never published; the reviewer writes the public wording Column("source_url", String), Column("lga_id", Integer), # reporter-chosen LGA (ref_lgas) Column("city", String), # optional free text Column("map_lat", Numeric(9, 6)), # optional point picked on the map, rounded to ~100 m Column("map_lng", Numeric(9, 6)), Column("status", String, nullable=False, default="new"), Column("incident_id", String, ForeignKey("incidents.incident_id")), Column("handled_by", String), Column("handled_at", TS), Column("review_note", Text), created(), _in("status", C.USER_REPORT_STATUS), CheckConstraint(f"length(description) <= {C.MAX_REPORT_LENGTH}", name="ck_user_reports_len"), ) admin_log = Table( "admin_log", metadata, Column("log_id", String, primary_key=True, default=new_id), Column("actor", String, nullable=False), Column("action", String, nullable=False), Column("target_type", String), Column("target_id", String), Column("detail", JSON), created(), ) @lru_cache(maxsize=1) def get_engine() -> Engine: s = get_settings() kwargs: dict = {"future": True} if s.backend == "local": kwargs["connect_args"] = {"check_same_thread": False} else: kwargs["pool_pre_ping"] = True engine = create_engine(s.database_url, **kwargs) if s.backend == "local": @event.listens_for(engine, "connect") def _fk(dbapi_conn, _): # noqa: ANN001 cur = dbapi_conn.cursor() cur.execute("PRAGMA foreign_keys=ON") cur.close() return engine def reset_engine() -> None: get_engine.cache_clear() def init_db(engine: Engine | None = None) -> None: eng = engine or get_engine() metadata.create_all(eng) add_missing_columns(eng) if eng.dialect.name == "postgresql": # idempotent; new tables must not be readable with the public key from sqlalchemy import text with eng.begin() as c: open_tables = {r[0] for r in c.execute(text( "SELECT tablename FROM pg_tables WHERE schemaname = current_schema() AND NOT rowsecurity"))} for t in metadata.sorted_tables: if t.name in open_tables: c.execute(text(f"ALTER TABLE {t.name} ENABLE ROW LEVEL SECURITY")) def add_missing_columns(engine: Engine) -> None: """Additive upgrade for databases created before a column existed (create_all never alters).""" from sqlalchemy import inspect, text from sqlalchemy.schema import CreateColumn insp = inspect(engine) with engine.begin() as c: for t in metadata.sorted_tables: if not insp.has_table(t.name): continue have = {col["name"] for col in insp.get_columns(t.name)} for col in t.columns: if col.name not in have: ddl = str(CreateColumn(col).compile(dialect=engine.dialect)) c.execute(text(f"ALTER TABLE {t.name} ADD COLUMN {ddl}"))