github-actions
Deploy API from 3ee9403
350a45d
Raw History Blame Contribute Delete
19 kB
"""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}"))