Spaces:
Sleeping
Sleeping
Download api/app/db.py from AustinWealth/Data-wiz-project: direct link, hf CLI and curl.
- Browser
- Download file 19 kB
-
https://huggingface.co/spaces/AustinWealth/Data-wiz-project/resolve/main/api/app/db.py
- Command line
-
hf download hf://spaces/AustinWealth/Data-wiz-project/api/app/db.py
-
curl -L -o db.py https://huggingface.co/spaces/AustinWealth/Data-wiz-project/resolve/main/api/app/db.py
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(), | |
| ) | |
| 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": | |
| 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}")) | |