Data model¶

One events row carries a geolocation for its whole life. The status it holds says how far along it is, four writes move or correct it, and the tables below hang off it rather than copy it.

flowchart LR
  classDef spec fill:#eef1fb,stroke:#4a5fa5,color:#33417a
  classDef shared fill:#e3f2f1,stroke:#0f7b7a,color:#0b5c5b
  classDef core fill:#0f7b7a,stroke:#083f3e,stroke-width:3px,color:#ffffff
  classDef store fill:#0b5c5b,stroke:#083f3e,color:#ffffff

  subgraph legend [Legend]
    direction LR
    l1["`a write that moves the row`"]:::spec
    l2["`where the row comes from`"]:::shared
    l3["`a status the one row holds`"]:::core
    l4[("`a table that hangs off it`")]:::store
    l1 ~~~ l2 ~~~ l3 ~~~ l4
  end

  ask["`**POST /events/requests**
  an open call to geolocate`"]:::shared
  submit["`**POST /events**
  a finished geolocation, born published`"]:::shared
  engine["`**persist_detections**
  the ingest engine: bot, paste or archive`"]:::shared

  requested["`**requested**
  requested_at; a coordinate is optional, so the ask may carry a guess`"]:::core
  detected["`**detected**
  detected_at; source_url optional, public from the moment it lands`"]:::core
  geolocated["`**geolocated**
  geolocated_at; ck_events_coords_status and ck_events_source_url_status make the coordinate and the source NOT NULL here`"]:::core
  closed["`**closed**
  closed_at, close_reason, and before_closed_status: withdrawn, rejected or retracted`"]:::core

  amend["`**update_request**
  the owner corrects the open question: overwritten in place, no version filed`"]:::spec
  vouch["`**services/events.geolocate**
  a person vouches: coordinate, source_url, one proof image. owner_id moves to the fulfiller`"]:::spec
  version["`**POST /events/{id}/versions**
  every later correction; refused past 100 versions`"]:::spec
  close["`**close**
  the owner takes the row back, from any live status`"]:::spec

  credit[("`**event_geolocators**
  durable credit, owner included`")]:::store
  versions[("`**event_versions**
  the superseded state, as version_no moves on`")]:::store
  reports[("`**content_reports**
  any viewer, signed in or not; a row names an event or a collection`")]:::store
  shelve["`**PUT /collections/{id}/events/{event_id}**
  the owner puts one of their own rows on a named shelf`"]:::spec
  shelves[("`**collections** + **collection_events**
  which curated sets the row is on`")]:::store

  ask --> requested
  engine --> detected
  submit --> vouch
  requested --> amend --> requested
  requested --> vouch
  detected --> vouch
  vouch --> geolocated --> version --> geolocated
  vouch --> credit
  version --> versions
  requested --> close
  detected --> close
  geolocated --> close
  close --> closed
  geolocated --> reports
  geolocated --> shelve
  detected --> shelve
  shelve --> shelves

The three entries on the left are the three ways a row is born: a request and a direct submit come from POST /events/requests and POST /events, and a machine detection comes from the ingest engine. The four statuses and the constraints that pin them are events. The four writes are update_request, geolocate, save_version and close, all in api.md. Two of them correct a row rather than move it, and they differ on what the row is: update_request overwrites an open question, while save_version files what it supersedes, because a published row is a vouched claim. The tables on the right exist because a write happened: event_geolocators records who vouched, event_versions holds what a correction superseded, content_reports holds what a viewer flagged, and collections with collection_events holds the curated sets the owner puts their own rows on.

Schema overview¶

erDiagram
    users {
        UUID id PK
        VARCHAR username
        VARCHAR email "nullable, legacy credential-less rows"
        VARCHAR password_hash "nullable, legacy credential-less rows"
        VARCHAR x_handle "nullable, UNIQUE, admin-linked bot-attribution handle"
        BOOLEAN is_active
        BOOLEAN is_admin
        TIMESTAMPTZ email_verified_at "nullable"
        TIMESTAMPTZ last_seen_at "nullable, throttled activity stamp"
        TIMESTAMPTZ deleted_at "nullable, soft-delete"
        INTEGER token_version "session-invalidation counter"
        TEXT bio "nullable, profile blurb"
        TEXT avatar_url "nullable"
        JSONB external_links "default {}, linktree-style"
        TIMESTAMPTZ created_at
    }

    admin_events {
        UUID id PK
        UUID actor_id FK "nullable"
        TEXT action
        JSON target "nullable"
        TIMESTAMPTZ created_at
    }

    auth_events {
        UUID id PK
        UUID user_id FK "nullable"
        TEXT event
        TIMESTAMPTZ created_at
    }

    auth_tokens {
        UUID id PK
        UUID user_id FK
        TEXT token_hash
        TEXT purpose "password_reset"
        TIMESTAMPTZ expires_at
        TIMESTAMPTZ consumed_at "nullable"
        TIMESTAMPTZ created_at
    }

    invite_codes {
        UUID id PK
        VARCHAR code
        UUID used_by FK "audit-only"
        TIMESTAMPTZ used_at "nullable, redemption stamp"
        TIMESTAMPTZ expires_at "nullable"
        TIMESTAMPTZ revoked_at "nullable"
        VARCHAR x_handle "nullable, bound handle copied at redemption"
        TIMESTAMPTZ created_at
    }

    pending_registrations {
        UUID id PK
        VARCHAR email UK
        VARCHAR username UK
        VARCHAR password_hash
        UUID invite_code_id FK
        TEXT token_hash UK
        TIMESTAMPTZ expires_at
        TIMESTAMPTZ created_at
    }

    events {
        UUID id PK
        UUID owner_id FK "edit-rights owner, moves to geolocator"
        UUID requested_by_id FK "nullable, who opened the request"
        VARCHAR title
        GEOMETRY event_coords "nullable, the subject; required at geolocated"
        GEOMETRY capture_source_coords "nullable, the camera position"
        TEXT source_url "nullable, required at requested/geolocated"
        BIGINT detected_from_tweet_id "nullable, detection provenance id"
        BIGINT_ARRAY detected_thread_tweet_ids "nullable, the thread's post ids"
        TEXT detected_from_url "nullable, detection provenance link"
        VARCHAR detected_via "nullable, bot | paste | archive"
        JSONB proof
        DATE event_date "nullable"
        TIME event_time "nullable, optional UTC hour"
        TIMESTAMPTZ source_posted_at "nullable, when the source posted (UTC)"
        TIMESTAMPTZ detected_post_at "nullable, analyst's X post time"
        TIMESTAMPTZ requested_at "nullable, entered requested"
        TIMESTAMPTZ detected_at "nullable, entered detected"
        TIMESTAMPTZ geolocated_at "nullable, entered geolocated"
        TIMESTAMPTZ closed_at "nullable, entered closed"
        VARCHAR status "requested | detected | geolocated | closed"
        TEXT close_reason "nullable, free-text"
        VARCHAR before_closed_status "nullable, requested | detected | geolocated"
        TIMESTAMPTZ deleted_at "nullable, admin soft-delete"
        TIMESTAMPTZ hidden_at "nullable, admin takedown"
        BOOLEAN is_graphic "death, injury or human remains"
        INTEGER version_no "which version the live row is, from 1"
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    event_versions {
        UUID id PK
        UUID event_id FK
        INTEGER version_no "the version this row holds"
        UUID edited_by_id FK "nullable, who superseded it"
        TEXT note "nullable, capped at 280 chars by the schema"
        JSONB snapshot "the editable fields as they stood, blank once redacted"
        TIMESTAMPTZ created_at "when the edit happened"
        TIMESTAMPTZ redacted_at "nullable, when an admin blanked it"
        UUID redacted_by_id FK "nullable, which admin did"
    }

    content_reports {
        UUID id PK
        UUID event_id FK "nullable, NULL once the event is hard-deleted"
        UUID collection_id FK "nullable, the other target; never set with event_id"
        VARCHAR reason "illegal_content | graphic_not_flagged | copyright | privacy | other"
        TEXT details "nullable, capped at 2000 chars by the schema"
        UUID reporter_user_id FK "nullable, anonymous reports leave this NULL"
        TIMESTAMPTZ created_at
        TIMESTAMPTZ resolved_at "nullable"
        VARCHAR resolution "nullable, marked_graphic | hidden | dismissed"
        UUID resolved_by FK "nullable"
    }

    event_geolocators {
        UUID event_id FK
        UUID user_id FK
        TIMESTAMPTZ created_at
    }

    event_source_links {
        UUID event_id FK
        INT position
        TEXT url
    }

    media {
        UUID id PK
        UUID event_id FK
        VARCHAR role "source | proof"
        TEXT storage_url
        VARCHAR media_type
        VARCHAR sha256 "nullable, hex-encoded SHA-256 of uploaded bytes"
        TEXT original_filename "nullable, client-supplied filename"
        TIMESTAMPTZ created_at
    }

    tags {
        UUID id PK
        VARCHAR name
        VARCHAR category "capture_source | free"
    }

    event_tags {
        UUID event_id FK
        UUID tag_id FK
    }

    conflicts {
        UUID id PK
        VARCHAR name "UNIQUE"
        VARCHAR wikidata_id "nullable, UNIQUE, sync/seed natural key"
        INT start_year "nullable"
        INT end_year "nullable"
        BOOLEAN ongoing
        TIMESTAMPTZ last_seen_at "nullable, last sync sighting"
        VARCHAR tier "nullable, major | minor | conflict"
        VARCHAR source "sync | seed | manual"
    }

    event_conflicts {
        UUID event_id FK
        UUID conflict_id FK
    }

    collections {
        UUID id PK
        UUID owner_id FK "the one owner"
        VARCHAR title
        JSONB description "what the collection holds, a Tiptap doc"
        TEXT description_text "its plain-text projection"
        TIMESTAMPTZ hidden_at "nullable, admin takedown"
        TIMESTAMPTZ created_at
        TIMESTAMPTZ updated_at
    }

    collection_events {
        UUID collection_id FK
        UUID event_id FK
        TIMESTAMPTZ added_at
    }

    follows {
        UUID follower_id FK
        UUID followed_id FK
        TIMESTAMPTZ created_at
    }

    bot_mentions {
        UUID id PK
        VARCHAR mention_tweet_id "UNIQUE, X snowflake"
        VARCHAR author_handle
        VARCHAR outcome
        INTEGER events_created
        VARCHAR reply_tweet_id "nullable"
        TIMESTAMPTZ processed_at
    }

    bot_webhook_events {
        UUID id PK
        JSONB mention
        VARCHAR status "queued | processing | done | failed"
        INTEGER attempts
        TIMESTAMPTZ created_at
    }

    archive_import_jobs {
        UUID id PK
        UUID owner_id FK
        TEXT zip_key
        VARCHAR status "queued | running | done | failed"
        INTEGER attempts
        INTEGER post_estimate "nullable"
        INTEGER progress_done
        INTEGER progress_total "nullable"
        INTEGER created_count
        INTEGER updated_count
        INTEGER skipped_count
        INTEGER failed_count
        TEXT error "nullable"
        TIMESTAMPTZ created_at
        TIMESTAMPTZ started_at "nullable"
        TIMESTAMPTZ finished_at "nullable"
    }

    source_archives {
        UUID id PK
        UUID event_id FK
        TEXT original_url
        VARCHAR origin "source_url | secondary_source | detected_from | proof_link"
        TEXT snapshot_url
        VARCHAR provider "wayback | archive_today | ghostarchive"
        TIMESTAMPTZ created_at
    }

    users ||--o{ invite_codes : "used_by"
    invite_codes ||--o{ pending_registrations : "invite_code_id"
    users ||--o{ auth_tokens : "user_id"
    users ||--o{ admin_events : "actor_id"
    users ||--o{ auth_events : "user_id"
    users ||--o{ events : "owner_id"
    users ||--o{ events : "requested_by_id"
    events ||--o{ media : "event_id"
    events ||--o{ event_tags : "event_id"
    tags ||--o{ event_tags : "tag_id"
    events ||--o{ event_conflicts : "event_id"
    conflicts ||--o{ event_conflicts : "conflict_id"
    events ||--o{ event_geolocators : "event_id"
    users ||--o{ event_geolocators : "user_id"
    events ||--o{ event_source_links : "event_id"
    events ||--o{ event_versions : "event_id"
    users ||--o{ event_versions : "edited_by_id"
    users ||--o{ event_versions : "redacted_by_id"
    events |o--o{ content_reports : "event_id"
    collections |o--o{ content_reports : "collection_id"
    users ||--o{ content_reports : "reporter_user_id"
    users ||--o{ content_reports : "resolved_by"
    users ||--o{ collections : "owner_id"
    collections ||--o{ collection_events : "collection_id"
    events ||--o{ collection_events : "event_id"
    users ||--o{ follows : "follower_id"
    users ||--o{ follows : "followed_id"
    users ||--o{ archive_import_jobs : "owner_id"
    events ||--o{ source_archives : "event_id"

Tables¶

`users`¶

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`username`	`VARCHAR(50)`	UNIQUE, NOT NULL
`email`	`VARCHAR(255)`	UNIQUE, nullable. NULL only on legacy credential-less rows; every path that creates an account sets it.
`password_hash`	`VARCHAR(255)`	nullable, on the same legacy rows as `email`.
`x_handle`	`VARCHAR(50)`	UNIQUE, nullable. The X handle the bot attributes mentions to (lowercase, no `@`). The system sets it at registration from an invite-bound handle, or an admin links it through `PATCH /admin/users/{id}/x-handle`. Analysts cannot self-serve this field, and the bot never creates rows for it. It differs from `external_links["x"]`, a display handle the owner sets and nobody verifies.
`is_active`	`BOOLEAN`	NOT NULL, default `true`
`is_admin`	`BOOLEAN`	NOT NULL, default `false`. The system flips this to `true` automatically on login or registration if the email matches `ADMIN_EMAILS`.
`email_verified_at`	`TIMESTAMPTZ`	nullable. Audit stamp written once by the registration confirmation (to `created_at`) and read by no code path. Every account the registration flow creates carries it.
`last_seen_at`	`TIMESTAMPTZ`	nullable. The instant of the account's most recent authenticated request. `dependencies.get_current_user` rewrites it once the stored value is older than `LAST_SEEN_THROTTLE` (15 minutes), so a session costs one UPDATE per window. `POST /auth/login` and `POST /auth/confirm-registration` stamp it when they issue cookies. The admin onboarding table reads it as the account's activity, falling back to the newest `login` auth event while it is NULL.
`deleted_at`	`TIMESTAMPTZ`	nullable. A non-NULL value marks the user as soft-deleted: login is rejected, the profile returns 404, and public reads filter the row out. Soft-deleting a user cascades to soft-delete every event they own. Hard-delete, the GDPR escape hatch, drops the user row, the events they own, and their contributor rows, and sweeps S3. Because the owner is always among an event's geolocators, hard-delete never leaves a `geolocated` event with zero geolocators.
`token_version`	`INTEGER`	NOT NULL, default `0`. A monotonic session-invalidation counter. The session JWT embeds this value as a `tv` claim, and `get_current_user` returns 401 on a mismatch. The system bumps this counter on logout, password change, password reset, and soft-delete, which invalidates every outstanding JWT for the user at once. A JWT with no `tv` claim also returns 401.
`bio`	`TEXT`	nullable. A short plain-text blurb shown on the public profile. Analysts edit it through `PATCH /users/me`. The API layer caps it at 500 characters. There is no database constraint, so changing the cap does not require a migration.
`avatar_url`	`TEXT`	nullable. The public URL of the analyst's profile picture, always an object on the media host. The server mints the value: `PUT /users/me/avatar` stores one metadata-stripped 400 px JPEG under `avatars/{user_id}/` and writes its URL here, `DELETE /users/me/avatar` clears the column and the object, and `PATCH /users/me` rejects the field, so the avatar cannot become a beacon that collects readers' IP addresses and User-Agents.
`external_links`	`JSONB`	NOT NULL, default `'{}'::jsonb`. A Linktree-style object keyed by platform (`x`, `discord`, `website`, `github`), each value validated and normalised on the way in (see `api.md`). The default `{}` means the value is never NULL, so the read path always gets a dict. `PATCH /users/me` replaces the whole column. A partial merge would conflict with the whole-panel form submit.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Indexes:

users_x_handle_key: UNIQUE on (x_handle). Enforces one account per X handle. Postgres allows unlimited NULLs, so handle-less rows are unaffected.
ix_users_live: partial index on (created_at) WHERE deleted_at IS NULL. Admin search and the auth path both filter on deleted_at IS NULL.
ix_users_search_fts: GIN index on to_tsvector('simple', coalesce(username, '') || ' ' || coalesce(bio, '')). Backs GET /search (the analyst branch). bio is part of the indexed expression so ts_headline can return a fragment highlight.

`auth_tokens`¶

Password-reset tokens. Each token is single-use.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`user_id`	`UUID`	FK → `users.id` ON DELETE CASCADE, NOT NULL
`token_hash`	`TEXT`	UNIQUE, NOT NULL. `sha256(secret)`. The plaintext token exists only in the email link.
`purpose`	`TEXT`	NOT NULL, CHECK in `('password_reset')`. `consume` matches on it, so a token minted for one purpose can never be redeemed for another.
`expires_at`	`TIMESTAMPTZ`	NOT NULL
`consumed_at`	`TIMESTAMPTZ`	nullable. A non-null value means the token was already redeemed.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Indexes:

ix_auth_tokens_user_id on (user_id). Supports cascade delete and per-user revocation lookups.
ix_auth_tokens_user_purpose on (user_id, purpose). Checks whether a live reset already exists for a user before minting a new one.
ix_auth_tokens_live_expires_at: partial index on (expires_at) WHERE consumed_at IS NULL. Keeps the reaper scan cheap as consumed rows accumulate.

Lifecycle: mint creates a token, consume redeems it, and revoke_all_live_for_user force-revokes prior tokens on a fresh same-purpose mint. Cleanup runs on demand from the admin Maintenance panel (services/maintenance.py::reap_auth_tokens). It deletes live-but-expired rows immediately and drops consumed rows older than 30 days.

`invite_codes`¶

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`code`	`VARCHAR(64)`	UNIQUE, NOT NULL
`used_by`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable, audit-only. Records the user who redeemed the code. The FK is set to NULL on user hard-delete.
`used_at`	`TIMESTAMPTZ`	nullable. Stamped at redemption. Every code is single-use, so a non-NULL value is what spends it. Unlike `used_by`, it survives the redeemer's hard-delete.
`expires_at`	`TIMESTAMPTZ`	nullable
`revoked_at`	`TIMESTAMPTZ`	nullable. The admin page sets this. A non-null value invalidates the code immediately.
`x_handle`	`VARCHAR(50)`	nullable. The X handle the code binds, normalized to lowercase with no `@`. Redemption copies it onto the new account's `users.x_handle`, the bot-attribution link. If the handle got linked elsewhere between mint and redemption, redemption fails soft.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

A code is valid exactly when revoked_at IS NULL AND used_at IS NULL AND (expires_at IS NULL OR expires_at > now()). used_by is not part of the validity check: it is nulled when the redeemer's account is erased, while used_at stays.

DELETE /admin/invite-codes/{id} drops a row whose used_by is NULL, the cleanup for codes that were minted and never used. A row naming a redeemer is refused: it is that account's origin record. The admin_events row for the deletion carries the code value, so the trail names what left the table.

`pending_registrations`¶

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`email`	`VARCHAR(255)`	UNIQUE, NOT NULL. Holds the address until the user confirms or the row expires.
`username`	`VARCHAR(50)`	UNIQUE, NOT NULL. Plays the same role as `email`, reserving the username until confirmation or expiry.
`password_hash`	`VARCHAR(255)`	NOT NULL. A bcrypt hash. The system transfers it directly into `users.password_hash` at confirmation.
`invite_code_id`	`UUID`	FK → `invite_codes.id` ON DELETE CASCADE, NOT NULL. The invite is referenced but not consumed until confirmation.
`token_hash`	`TEXT`	UNIQUE, NOT NULL. `sha256(secret)`. The plaintext token exists only in the email link.
`expires_at`	`TIMESTAMPTZ`	NOT NULL. Set to 24 hours after mint.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Indexes:

ix_pending_registrations_expires_at on (expires_at). Supports the reaper sweep.

Why UNIQUE on email and username instead of a partial index? Partial-index predicates must be IMMUTABLE in Postgres, and expires_at > now() is STABLE. The plain UNIQUE constraint keeps race-window protection without a predicate. The /auth/register path deletes expired rows inline before its INSERT, and the admin Maintenance reaper sweeps the rest. As a result, a recently expired pending row does not permanently reserve its address.

Lifecycle: POST /auth/register inserts a row through services/registration.py::create_pending_registration. POST /auth/confirm-registration consumes it through confirm_pending_registration, which creates the users row, copies a bound invite_codes.x_handle onto it if the handle is still free, stamps invite_codes.used_at, and deletes the pending row. POST /auth/resend-confirmation re-mints the token on the same row and invalidates the previous link. Cleanup runs through services/registration.py::reap_pending_registrations, exposed in the admin Maintenance panel.

`auth_events`¶

An append-only audit log for auth-relevant events. The auth router populates it synchronously on login, failed_login, logout, register_pending, register_resent, register_confirmed, password_reset_requested, password_reset_completed, and password_changed. Writes happen inside a SAVEPOINT (db.begin_nested()), so an INSERT failure rolls back only the audit row and the caller's transaction stays usable.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`user_id`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable. NULL when the email didn't match a live user: `failed_login`, the `password_reset_requested` no-op branch, `register_pending`, both branches of `register_resent`, and anonymous logout. This prevents the row from doubling as a probe-able email-to-existence oracle.
`event`	`TEXT`	NOT NULL. A plain string, not a database enum, so adding a new event kind doesn't require a migration.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Indexes:

ix_auth_events_user_id_created_at on (user_id, created_at). Supports the forensics query "what did this user do, latest first".
ix_auth_events_event_created_at on (event, created_at). Supports the query "did event X spike recently".

The table stores no IP address or User-Agent. Vidit drops them for privacy; network context lives only at the Cloudflare edge. There is no retention policy.

`admin_events`¶

An append-only audit log for admin actions taken through the /admin page. It is a sibling to the auth_events table above.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`actor_id`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable
`action`	`TEXT`	NOT NULL, for example `invite_created`, `invite_revoked` and `invite_deleted`
`target`	`JSON`	nullable. Free-form context: target IDs and parameters.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Indexes:

ix_admin_events_actor_id on (actor_id). Supports the query "what did this admin do?".
ix_admin_events_created_at on (created_at). Supports chronological scans.

`events`¶

One row represents one event across its whole lifecycle. status tracks the lifecycle. event_coords is an independent nullable field tied to status by a CHECK constraint. A request is a requested event on this table, with no coordinates required yet. Fulfilling a request is a single UPDATE status='geolocated', event_coords=... on the same row plus an event_geolocators insert. It never copies the row into a new one.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`owner_id`	`UUID`	FK → `users.id`, NOT NULL. The edit-rights owner. For a `requested` event, this is the poster. It moves to the fulfiller at the `geolocated` transition, so permission checks stay a single-owner check across the lifecycle. The owner is always among the event's geolocators; see `event_geolocators`.
`requested_by_id`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable. Records who opened the request. This is preserved across fulfilment so the identity of the requester is not erased. NULL for a directly submitted geolocation.
`title`	`VARCHAR(255)`	NOT NULL
`event_coords`	`GEOMETRY(Point, 4326)`	nullable. The subject: what the footage shows. Tied to `status` by `ck_events_coords_status`: required for `geolocated`, optional otherwise. A `requested` request may carry an approximate guess. Each event has one subject point.
`capture_source_coords`	`GEOMETRY(Point, 4326)`	nullable. The camera position: where the footage was shot from. Always optional, one per event.
`source_url`	`TEXT`	nullable. Where the footage was first published. Tied to `status` by `ck_events_source_url_status`: required for `requested` and `geolocated`, optional for `detected`. A machine detection may declare no source; see `ingestion.md`.
`detected_from_tweet_id`	`BIGINT`	nullable. The ID of the post a machine detection was imported from. It anchors the display link and is the post-ID leg of the re-import match: one post spells its URL several ways, so the ID keeps two spellings on one detection. NULL for human submits. Indexed with `owner_id`, partial on the populated cohort.
`detected_thread_tweet_ids`	`BIGINT[]`	nullable. Every post ID of the thread the detection was read from, the anchor included. The re-import match reads it: a detection matches a row when their post-ID sets intersect. Written once at creation, like the other provenance columns. NULL for human submits; some machine rows carry their anchor ID alone. GIN-indexed, partial on the populated cohort.
`detected_from_url`	`TEXT`	nullable. The post a machine detection was imported from, as a link an analyst can open: the display value, written from `detected_from_tweet_id` at the engine's exit. A provenance link, distinct from `source_url`. NULL for human submits.
`detected_via`	`VARCHAR(20)`	nullable, `ck_events_detected_via_valid`: `'bot'`, `'paste'` or `'archive'`, the ingest entry that produced the row (see `ingestion.md`). Stamped once at creation by the shared write path and never moved, so a re-import through another entry does not rewrite where the row first came from. Read-only on `EventRead`. NULL for human submits and on some machine rows. This and the three `detected_from_*` columns above are stamped on a `requested` row the bot opened as well as on a detection, which is what lets a second tag on the same post recognise it.
`proof`	`JSONB`	NOT NULL. A Tiptap document stored as ProseMirror JSON. Every row carries a proof document: a human submit carries the analyst's write-up, and a machine detection carries the tweet or thread text. A submission with no proof body stores an empty document, not NULL.
`event_date`	`DATE`	nullable in every status. When the depicted event happened. NULL when unknown: the footage doesn't always establish the date, and it renders as Unknown. For a machine detection, this is provisionally the originating tweet's post date; the owner corrects it at submit.
`event_time`	`TIME`	nullable. An optional time of day for `event_date`, in UTC. NULL when the hour is unknown.
`source_posted_at`	`TIMESTAMPTZ`	nullable. When the original source posted the media: a real post instant, so a full UTC timestamp when known. Distinct from `event_date` (when the event happened), `detected_post_at` (when the analyst posted the geolocation), and `created_at` (when the row was submitted). A human submit or a machine detection with a quoted source always sets it. A machine detection with only a footage link and no quote leaves it `NULL`, because the link carries no date, except a Telegram footage link whose public embed was chased. That case carries the post's own date; see `ingestion.md`.
`detected_post_at`	`TIMESTAMPTZ`	nullable. When the analyst published this on X: the post time of `detected_from_url`, whether the row is a detection or a request the bot opened. This is the precedence input for the "who geolocated it first" claim/dispute pipeline. The system captures it at import, because the tweet may later be deleted. NULL for human submits.
`requested_at`	`TIMESTAMPTZ`	nullable. Stamped when the event entered `requested`.
`detected_at`	`TIMESTAMPTZ`	nullable. Stamped when a machine produced it, entering `detected`.
`geolocated_at`	`TIMESTAMPTZ`	nullable. Stamped when a person vouched for it and published it, entering `geolocated`.
`closed_at`	`TIMESTAMPTZ`	nullable. Stamped when the event entered the terminal `closed` state.
`status`	`VARCHAR(20)`	NOT NULL, `server_default 'geolocated'`. The lifecycle runs `requested` (an open call to geolocate) → `detected` (a machine detection, marked on every surface, immutable until vouched) → `geolocated` (a person vouched for it and published it; always has a location, and every later correction is a version) → `closed` (the owner took the row back, from any of the three live states). It is a plain string, not a native enum, and `ck_events_status_valid` pins the value domain. The default keeps a direct human submit correct without setting the value explicitly; the requested and detected paths pass `status` explicitly. The `update_request`, `geolocate`, `save_version` and `close` writes are documented in `api.md`.
`close_reason`	`TEXT`	nullable. A free-text reason the event was closed, such as AI image, bot bug, or withdrawn. Required by the close endpoint and kept visible for transparency, which is what makes a closed row read as a decision rather than a disappearance.
`before_closed_status`	`VARCHAR(20)`	nullable. The status held just before `closed`: `requested` means withdrawn, `detected` means rejected, `geolocated` means retracted. Drives the status badge and the read views: a rejected detection stays in the located catalog, a withdrawn request in the requested queue, and a retraction is in neither.
`deleted_at`	`TIMESTAMPTZ`	nullable. A non-NULL value marks an admin soft-delete: the row and its media stay in place, but every public read filters it out, admins included.
`hidden_at`	`TIMESTAMPTZ`	nullable. A non-NULL value marks a takedown: the row is withheld from every public read the same way `deleted_at` is, but an admin still reads it (judging the content report that led to the takedown means seeing what was withheld), and the state is reversible, which is what separates it from `deleted_at`. Set by `POST /admin/reports/{id}/resolve` (`resolution = "hidden"`) or directly by `PATCH /admin/events/{id}/moderation`; cleared only by the latter.
`version_no`	`INTEGER`	NOT NULL, `server_default 1`. Which version of the event the live row is. It starts at 1 and moves forward one step per correction, which files the superseded state in `event_versions`. Only a `geolocated` row can move past 1, because saving a version is the published-row correction path; see `POST /events/{id}/versions`. An open request is corrected in place by `POST /events/{id}/request` and stays at 1: a version supersedes a vouched claim, and a request is a question.
`is_graphic`	`BOOLEAN`	NOT NULL, default `false`. `TRUE` when the footage shows death, injury or human remains. The author sets it on the create / edit forms; an admin can override it, directly (`PATCH /admin/events/{id}/moderation`) or by resolving a report as `marked_graphic`. Public column, carried by every event read schema: the frontend covers a flagged event's media behind `GraphicContentGate` until the viewer confirms they want to see it.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`
`updated_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Check constraints: - ck_events_status_valid: status IN ('requested', 'detected', 'geolocated', 'closed'). Pins the value domain at the database. The column is a plain VARCHAR, not a native enum, so this constraint rejects a bad write at the Postgres level, not only at the app-layer Literal. - ck_events_detected_via_valid: detected_via IS NULL OR detected_via IN ('bot', 'paste', 'archive'). The same reason as the status domain, for the ingest entry. NULL is in-domain: a human submit names no entry. - ck_events_coords_status: status <> 'geolocated' OR event_coords IS NOT NULL. A geolocated event always has a subject coordinate. The other states leave it free, so a requested request may carry an approximate guess. - ck_events_source_url_status: status NOT IN ('requested', 'geolocated') OR source_url IS NOT NULL. A requested or geolocated event always has a source URL. A detection may carry none; see ingestion.md. The promotion to geolocated enforces the same rule in services/events.geolocate before the row can violate this CHECK. - ck_events_closed_stamp (status <> 'closed' OR closed_at IS NOT NULL) and ck_events_geolocated_stamp (status <> 'geolocated' OR geolocated_at IS NOT NULL). These tie the terminal stamps to status. An app path that forgets to stamp is rejected at write time, not stored as silent bad data. - ck_events_before_closed_status: (status = 'closed' AND before_closed_status IS NOT NULL AND before_closed_status IN ('requested', 'detected', 'geolocated')) OR (status <> 'closed' AND before_closed_status IS NULL). The field is non-NULL and in-domain exactly when closed, and NULL otherwise. The explicit IS NOT NULL clause is required: NULL IN (...) evaluates to unknown, not false, so without it a closed row could keep a NULL discriminator and slip through.

Temporal fields: four kinds of time. A geolocation carries several timestamps. Each marks a different point on the path from an event to a Vidit row. They are distinct by design:

event happens ──▶ source posts the media ──▶ analyst posts the geoloc on X ──▶ imported to Vidit
 event_date           source_posted_at            detected_post_at                 created_at
 (+ event_time)       (UTC instant)               (UTC instant, machine path)      (UTC instant)

Field	Meaning	Filled by	Null?
`event_date` (+ `event_time`)	when the depicted event happened	analyst, or detection (tweet date)	date nullable in every status: NULL when the footage doesn't establish it. Time optional: the hour is often unknown.
`source_posted_at`	when the source posted the media	analyst, or detection (a quoted source's date)	nullable. `NULL` on a `detected` row whose source is a footage link with no date, or whose source is undeclared.
`detected_post_at`	when the analyst posted it on X	machine-written rows only (the imported post's time)	NULL for human submits
`created_at`	when it was submitted to Vidit	system	NOT NULL

event_date is editorial: a real-world event, often known only to the day, with no canonical time zone. It stores a bare date plus an optional UTC hour. source_posted_at and detected_post_at are post instants: known to the minute when present, and always UTC, so they store full timestamps. All entered times follow the UTC convention.

Indexes: - GIST(event_coords). Required for geospatial queries: bounding-box filtering and proximity sort. capture_source_coords is not indexed, because no spatial read consumes it. - (owner_id). Supports profile lookup. - (event_date) and (created_at). Support time-based queries. - (owner_id, created_at DESC). A composite index for profile listing. Single-author reads key on owner_id. - ix_events_live: partial index on (created_at) WHERE deleted_at IS NULL. Every public read filters on deleted_at IS NULL, and the partial index keeps it tight. - ix_events_status_created_at on (status, created_at). The requested view, the map, and the detection queue all filter on status, newest first. - ix_events_created_at_id on (created_at, id). Backs the keyset that the capped list endpoints page on. GET /events, GET /events/detections, and GET /timeline order by created_at DESC, id DESC and cut each page with a row comparison over that pair; see api.md. - ix_events_detected_from_url: partial index on (detected_from_url) WHERE detected_from_url IS NOT NULL. Backs the admin machine-detection cohort scans, which count the rows carrying a provenance link at all. Human rows are always NULL here. - ix_events_owner_detected_from_tweet_id: partial index on (owner_id, detected_from_tweet_id) WHERE detected_from_tweet_id IS NOT NULL. Backs the post-id leg of the re-import match, one lookup per detection during a backfill, scoped to the importer's own rows. - ix_events_detected_thread_tweet_ids: partial GIN index on (detected_thread_tweet_ids) WHERE detected_thread_tweet_ids IS NOT NULL. Backs the array-overlap leg of the re-import match, one lookup per detection during a backfill. - ix_events_search_fts: GIN index on to_tsvector('simple', coalesce(title, '')). Backs GET /search; both the located and requested views run through it. The simple configuration, not english, keeps matching predictable for the corpus of place names and analyst handles. Soft-delete is filtered at query time. source_url is not in the indexed expression, because Postgres' simple parser tokenizes URLs as host and path units.

event_coords and capture_source_coords are PostGIS points in WGS84 (SRID 4326, standard GPS coordinates). GeoAlchemy2 exposes them as .lat and .lng through WKBElement, or as ST_X and ST_Y in raw SQL.

`event_geolocators`¶

Durable credit for the geolocation: who vouched for the location. owner_id is always among these rows. The system writes at least one row at the geolocate transition, and the credit is collaborative: an event can have many geolocators.

Column	Type	Constraints
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE
`user_id`	`UUID`	FK → `users.id` ON DELETE CASCADE
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

Composite PK: (event_id, user_id). Makes the credit idempotent.

Indexes: - ix_event_geolocators_user_created_at on (user_id, created_at). Supports the reverse query, a user's geolocations, for the profile page.

The composite PK's leading event_id serves the forward read, who geolocated event X. Because owner_id is always among the geolocators, and hard_delete_user deletes the events a user owns, erasing a user can never leave a geolocated event with zero geolocators.

`event_versions`¶

One superseded version of a published event. Correcting a geolocated row must not silently rewrite the record, so the pre-edit state is filed here before the edit lands and events.version_no moves on. A version number is a public address, so a filed row is never removed and never renumbered while its event lives: nothing reuses a number, nothing reorders the history, and /events/{id}/vN means the same thing forever. Redaction is the one write a filed row takes (below), and the cascade with its event is the one delete.

Read it as "the snapshots, then the live row": an event at version_no 3 carries rows 1 and 2 here, and the live row is version 3. Publication writes nothing here, so version 1 is the published row itself.

One write files a version, through services/versions.file_version: the owner's edit (POST /events/{id}/versions), which is also where an archived copy of one of the row's links is recorded, since that is a change to what the published record says about its own evidence. The number is taken under the event's row lock.

An event carries at most 100 versions (services/versions.MAX_VERSIONS_PER_EVENT); a save whose only change is archived copies is exempt. api.md states the refusal. A version has to differ from the row it supersedes, so a save that moves no versioned field and no archived copy files nothing.

Redaction is that one write, and it is still not a delete: an admin blanks snapshot and note in place and stamps redacted_at / redacted_by_id, so the row, its number and its byline stay. See POST /admin/events/{id}/versions/{version_no}/redact.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE, NOT NULL
`version_no`	`INTEGER`	NOT NULL. The version this row holds, not the one that replaced it.
`edited_by_id`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable. The analyst whose edit superseded this version. NULL once that account is erased: an event legitimately outlives an editor who is not its owner.
`note`	`TEXT`	nullable. The editor's own words about the edit. Bounded to 280 characters by the schema, not the column, which stays unbounded `TEXT`.
`snapshot`	`JSONB`	NOT NULL. The editable fields as they stood: `title`, `source_url`, `source_media`, `event_coords`, `capture_source_coords`, `event_date`, `event_time`, `source_posted_at`, `is_graphic`, `secondary_source_urls`, `tags`, `conflicts`, `proof`, `proof_media`, and `archives`. `source_media` and `proof_media` carry each media whole (`id`, `role`, `storage_url`, `media_type`, `sha256`, `original_filename`); `proof_media` holds the images this version's own proof body referenced, not every `proof` row the event holds. `archives` carries the version's archived copies (`original_url`, `origin`, `snapshot_url`, `provider`, `created_at`), sorted by `original_url` so a stored list never reshuffles between versions. Tags and conflicts carry their names alongside their ids, so a version stays readable after a referential row is renamed. `{}` on a redacted version.
`created_at`	`TIMESTAMPTZ`	NOT NULL. When the edit that superseded this version happened.
`redacted_at`	`TIMESTAMPTZ`	nullable. When an admin blanked this version's content. NULL on every ordinary row.
`redacted_by_id`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable. Which admin redacted it. NULL once that account is erased; the readable trail is the `admin_events` row the same write files.

Unique constraint: uq_event_versions_event_no on (event_id, version_no). The writer takes the number off the locked event row, so a duplicate is a bug Postgres rejects rather than a second history entry for one version.

There is no secondary index: the only read is "this event's history, by version", served by the unique constraint's leading event_id.

Media rows are not versioned; the files they name outlive them. A media row a readable snapshot's proof_media points at is never hard-deleted, so a past version stays renderable after the current proof body drops the image. The shared intake checks this before it deletes a proof row; see services/versions.referenced_media_urls.

The source media cannot take that route: uq_media_source_per_event allows one per event, so a correction that swaps the anchor deletes the row it replaces. The snapshot carries that media whole instead, and its S3 object is left in place, which is what keeps /vN renderable; services/versions.referenced_source_media is what resolves those objects for the sweeps that would otherwise orphan or delete them. A redacted version displays nothing, so it holds nothing alive: redacting the last version that showed a proof image deletes that image, row and object, and the last one that named a superseded source frees its file.

`event_source_links`¶

An event's ordered secondary source links: mirrors of the same footage on another network, or another post from the same point of view. The primary source stays the scalar events.source_url, which a requested row protects from its fulfiller; these rows are optional extras with no such protection. See api.md.

Column	Type	Constraints
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE
`position`	`INTEGER`	list index, `0`-based
`url`	`TEXT`	NOT NULL

Composite PK: (event_id, position). position sits in the key, so the stored order is the read order, and Postgres rejects a duplicate slot.

There is no secondary index: every read is "this event's links, in order", served by the PK's leading event_id. MAX_SECONDARY_SOURCE_LINKS = 10 (backend/app/models/event.py) caps how many rows an event carries. The write forms normalize and enforce this cap before insert: they strip whitespace, drop blanks, drop duplicates, and drop the entry equal to source_url, preserving order.

The system writes this list wholesale, not row by row. A create sets the full ordered list once, and every later write replaces the whole list with whatever the form submits: the requester's own edit of an open request, and the geolocate, including for requested events. Unlike source_url, there is no requester protection here. Hard-deleting the event cascades to the rows.

`content_reports`¶

One viewer's report against one event or one collection. Open to anonymous viewers: a takedown request must not require an account, since the people a piece of footage harms are rarely the people who hold one. Rows accumulate rather than dedupe: several viewers may report the same target, and each report is resolved on its own. A report is never deleted, only resolved, so the table is an audit trail of what was reported and what was decided.

One row names one target, through event_id or collection_id. Two real foreign keys rather than a (target_type, target_id) pair, so the database is what keeps a report pointing at a row that exists and what empties the pointer when that row is destroyed.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`event_id`	`UUID`	FK → `events.id` ON DELETE SET NULL, nullable. NULL once the reported event is hard-deleted: the report is the record that a complaint was filed and how it was answered, so it outlives the event. NULL too on a report filed against a collection. An orphaned report accepts only the `dismissed` verdict; every other verdict mutates a row that is gone, and answers 409 `report_target_gone`.
`collection_id`	`UUID`	FK → `collections.id` ON DELETE SET NULL, nullable. The other target, on the same terms as `event_id` above and NULL for a report filed against an event. It empties when the collection goes, which a collection only does with its owner's account (`collections.owner_id` cascades), and the report survives that erasure the way it survives an event's.
`reason`	`VARCHAR(30)`	NOT NULL, CHECK in `('illegal_content', 'graphic_not_flagged', 'copyright', 'privacy', 'other')`. `illegal_content` is the legal escalation (material whose hosting is itself unlawful); `graphic_not_flagged` says the footage shows death, injury or human remains without the author's `events.is_graphic` declaration; `copyright` and `privacy` are third-party rights claims; `other` keeps the form answerable when none of the four fits, with `details` carrying the story.
`details`	`TEXT`	nullable. The reporter's own words. Bounded to 2000 characters by the schema, not the column, which stays unbounded `TEXT`.
`reporter_user_id`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable. NULL for an anonymous report, and again once the reporter's account is erased (the report outlives the account, including a GDPR erasure).
`created_at`	`TIMESTAMPTZ`	NOT NULL. The application stamps it on insert; the column carries no server default, so a raw `INSERT` must supply it.
`resolved_at`	`TIMESTAMPTZ`	nullable. Non-NULL exactly when `resolution` is (`ck_content_reports_resolution_stamp`), so `resolved_at IS NULL` is the single-column test for an open report.
`resolution`	`VARCHAR(30)`	nullable, CHECK in `('marked_graphic', 'hidden', 'dismissed')` when set. `marked_graphic` sets `events.is_graphic`, `hidden` withholds the target from every public read via `events.hidden_at` or `collections.hidden_at`, `dismissed` closes the report and leaves the target untouched. `marked_graphic` is an event verdict only: the flag is a column on the event, so a collection report answering it is a 409 `report_verdict_not_applicable` rather than a verdict that changed nothing. There is no re-resolve: a report already carrying a verdict answers a second resolve attempt with 409.
`resolved_by`	`UUID`	FK → `users.id` ON DELETE SET NULL, nullable. The admin who resolved it, NULL until then and again after a GDPR erasure of that admin's account.

Check constraints: - ck_content_reports_reason_valid: pins the reason domain at the database, mirroring the ContentReportReason alias so a bad write is rejected by Postgres, not only by the app-layer Literal. - ck_content_reports_resolution_valid: pins the resolution domain the same way, mirroring ContentReportResolution. - ck_content_reports_resolution_stamp: (resolution IS NULL AND resolved_at IS NULL) OR (resolution IS NOT NULL AND resolved_at IS NOT NULL). The verdict and its timestamp travel together in both directions: a resolved row can't forget what was decided, and an open row can't carry a stale verdict. - ck_content_reports_one_target: num_nonnulls(event_id, collection_id) <= 1. One report names one thing, so no queue row can describe two at once. The test is "never both" rather than "exactly one" because both columns are SET NULL: a report whose target is destroyed ends up naming neither, and that orphan row has to stay legal. Exactly one is set at insert, which the two report routes hold by each naming one target.

Indexes: - ix_content_reports_event_id on (event_id). Backs the FK's ON DELETE SET NULL sweep and a per-event report lookup. - ix_content_reports_collection_id on (collection_id). The same pair of reads for the other target. - ix_content_reports_queue: expression index on ((resolved_at IS NOT NULL), created_at DESC, id DESC). Backs the admin queue's read, open reports first then newest first, with the id breaking ties so the offset walk is total. The index repeats the query's ORDER BY expression for expression, so Postgres walks it instead of sorting the table. Change the sort and you change this index.

`follows`¶

Directed follow edges between analysts. Drives the per-user GET /timeline feed and the followers_count, following_count, and is_following fields on GET /users/{username}.

Column	Type	Constraints
`follower_id`	`UUID`	FK → `users.id` ON DELETE CASCADE, NOT NULL
`followed_id`	`UUID`	FK → `users.id` ON DELETE CASCADE, NOT NULL
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `CURRENT_TIMESTAMP`

Composite PK: (follower_id, followed_id). The pair is the natural identity, so there is no surrogate id. The PK alone gives uniqueness, so the schema ships no separate UNIQUE constraint.

Self-follow is rejected at two layers. The router returns 400 Cannot follow yourself, and CHECK (follower_id <> followed_id) (constraint ck_follows_no_self_follow) refuses the INSERT even if a code path skips the router. The 400 response gives the UI a clean error, and the CHECK is the durable invariant.

Indexes: - ix_follows_followed_id on (followed_id). The PK indexes the forward direction, who is X following, on its leading column. Without this index, the reverse direction, who follows X, the query that powers followers_count on every profile load, would full-scan.

ON DELETE CASCADE applies to both FKs. Hard-deleting an analyst drops every edge on either side, so a deleted user cannot keep ghost followers or ghost followings. Soft-deleted users (users.deleted_at IS NOT NULL) keep their edges. The public profile returns 404 regardless, and resurrecting an account should resurrect its graph.

`collections`¶

A named, curated set of one analyst's own events, shown on the owner's public profile: a spatial dossier or an operation reconstruction. Personal, with exactly one owner and no collaborators. Two written fields say what it is, the title and a required description, and the items order themselves by when their events happened, so the table carries no manual position, no denormalized count and no version history.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`owner_id`	`UUID`	FK → `users.id` ON DELETE CASCADE, NOT NULL. The one owner. Cascades, unlike `events.owner_id`: a collection is one analyst's own shelf and nothing on it outlives their account, so a GDPR hard delete passes straight through and leaves no stored object behind, a collection holding no file of its own.
`title`	`VARCHAR(255)`	NOT NULL. The same width as `events.title`, from the shared `TITLE_MAX_LENGTH` in `models/event.py`, so one cap governs an event title and a collection title alike. The API floor is 1 character.
`description`	`JSONB`	NOT NULL. What the collection holds, written by the owner on the create and on every edit as a Tiptap (ProseMirror) document, the same shape and the same sanitizer `events.proof` takes, minus images.
`description_text`	`TEXT`	NOT NULL. The plain-text projection of `description`, written beside it on every write from `services/sanitize.tiptap_doc_text`. Stored rather than derived per read because the collections search index is a GIN over it: a `to_tsvector` over the `JSONB` would index node names and punctuation instead of the words the owner wrote. It is also what a surface with no room for rich text prints, and what the API caps at 500 characters, the figure `users.bio` takes for the same class of body; there is no database constraint, so changing the cap does not require a migration. The API floor is 1 character after whitespace is stripped.
`hidden_at`	`TIMESTAMPTZ`	nullable. Takedown: NULL = visible, timestamp = withheld from every read but an admin's, the owner's included. The same reversible axis `events.hidden_at` carries. Set by `DELETE /admin/collections/{id}`, by `PATCH /admin/collections/{id}/moderation` with `hidden: true`, or by resolving a content report filed against the collection as `hidden`. Cleared only by `PATCH /admin/collections/{id}/moderation` with `hidden: false`.
`created_at`	`TIMESTAMPTZ`	NOT NULL
`updated_at`	`TIMESTAMPTZ`	NOT NULL, SQLAlchemy `onupdate` stamp

Indexes: - ix_collections_owner_created_at on (owner_id, created_at). Backs the profile section's only read, this analyst's collections newest first. - ix_collections_search_fts, a GIN over to_tsvector('simple', coalesce(title, '') || ' ' || coalesce(description_text, '')). Backs the collections group of GET /search. The expression has to stay identical to the one services/search._collection_tsvector builds, config name included, or the planner drops the index and scans the table.

The description is a document, and its projection is a column. The pair is written together in services/collections, on the create and on every edit, which is also where the document is sanitized and judged, so nothing can store a document whose stored projection describes different words. See api.md for the node and mark allowlist, and for what a refused description answers.

What a collection shows is one predicate, not a column. services/event_filters.collectable_events is a visible event (deleted_at IS NULL AND hidden_at IS NULL) in one of the two worked statuses, geolocated or detected. The item list, the item count, the date range, the card mosaic and the eligibility check the add verb runs all read it, so an event that later closes or is taken down leaves all five at once with no write to collection_events. A requested row is an ask rather than an answer; a closed row is one the owner rejected or retracted, and a curated shelf must not go on presenting it as work that stands.

Counts and the date range are computed per read. event_count is the number of showable items, first_date and last_date the smallest and largest event_date among them. Nothing is stored, so no write path can leave a stale figure behind.

The card mosaic is computed, not stored. A collection carries no cover column and no cover object. The cover field of the read is up to four tiles taken off the items themselves: walk the showable items in chronological order, skip one flagged is_graphic, take each remaining item's card media (services/thumbnails.pick_thumbnail, preferring an image over a clip on an item carrying both), and stop at four. A graphic item is skipped rather than ending the walk, so a card never shows death or injury to a reader who did not open the item. The list is empty when no item qualifies. See api.md.

The ownership invariant lives in the service. An event joins its owner's collection only, which spans two tables and so is no CHECK: services/collections.add_event enforces it, and an attempt to shelve somebody else's event is a 403. See api.md.

`collection_events`¶

One membership: this event is on this collection.

Column	Type	Constraints
`collection_id`	`UUID`	FK → `collections.id` ON DELETE CASCADE
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE
`added_at`	`TIMESTAMPTZ`	NOT NULL. When the owner put the event on the collection. Not a read key: the list orders by when the events happened, not by when they were shelved.

Composite PK: (collection_id, event_id). The pair is the identity, so adding an event twice is the same row and the add verb is idempotent without a read-then-write.

Indexes: - ix_collection_events_event_id on (event_id). The PK's leading collection_id serves the forward read, what is on this collection; this covers the reverse, which of my collections hold this event, behind GET /events/{id}/collections.

Both foreign keys cascade, so neither a hard-deleted event nor a hard-deleted collection leaves a membership pointing at nothing. Dropping a collection removes its memberships and touches no event: a collection is a view over the analyst's published record, so removing the view is not a judgement on any geolocation.

`media`¶

Every uploaded file for an event, source footage and proof-body images alike, split by role. Each row has one event_id owner. A request is a requested event, so all evidence lives on one table, and fulfilling a request never moves media.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE, NOT NULL. Always set. Files upload at publish, so there is no unattached staging row; see Upload timing below.
`role`	`VARCHAR`	NOT NULL. `'source'` for the footage, at most one per event, enforced by a partial unique index, or `'proof'` for inline images referenced from the proof body, with no per-event limit.
`storage_url`	`TEXT`	NOT NULL. An S3 or CloudFront URL.
`media_type`	`VARCHAR(10)`	NOT NULL, `'image'` or `'video'`. The stored MIME type is not a column: an analyst's upload keeps the accepted type it arrived as, and a machine-imported photo is re-encoded to one format at ingest whichever entry read the post; see `ingestion.md`.
`sha256`	`VARCHAR(64)`	nullable. Hex-encoded SHA-256 of the uploaded bytes, captured at upload time. A stable content fingerprint that survives storage-class changes and copies, unlike the S3 ETag, which is an MD5 for non-multipart uploads and is not stable across copies. The hash is computed on the bytes that land on S3, for images after the EXIF strip, so an auditor downloading the public URL can independently verify it.
`original_filename`	`TEXT`	nullable. The client-supplied filename, for example `IMG_1234.jpg`. Surfaced on the public read API so investigators can trace evidence back to a source post by filename.
`created_at`	`TIMESTAMPTZ`	NOT NULL, default `now()`

uploaded_ip and uploaded_user_agent are not stored. Vidit drops them for privacy; network context lives only at the Cloudflare edge.

Indexes: - (sha256) WHERE sha256 IS NOT NULL: a partial index for "find every row with this content hash" audit and dedup queries. Covers only the rows that carry a hash. - unique (event_id) WHERE role = 'source'. Enforces the "at most one source media per event" cap.

Each request and each geolocated event requires at least one source media row. The geolocate transition requires at least one proof image. A requested event carries the poster's evidence from the start.

Upload timing. Persistence happens only at publish. While the analyst writes, the proof editor holds local previews. Submit uploads every file, source and proof, through the same evidence intake, in one transaction. As a result, event_id is always set: there is no staging table, no event_id IS NULL orphan, and no proof-image reaper.

`tags`¶

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`name`	`VARCHAR(100)`	UNIQUE, NOT NULL
`category`	`VARCHAR(20)`	NOT NULL, `'capture_source'` or `'free'`

Tags with category capture_source describe the original "lens" that captured the media: Smartphone, Satellite, Drone, Static camera, Dashcam, Body / helmet cam, plus an Unknown escape value. The baseline migration seeds them, since the category is required on the submit form and the options must exist on a fresh database. Tags with category free are user-created and free-form.

Conflicts are not a tag category. They live in the dedicated conflicts table and link to events through event_conflicts.

capture_source is curated, server-managed and not user-creatable, and required: a submission must carry at least one capture_source tag and at least one conflict; see api.md → POST /events. The API layer enforces this rule, not a database constraint. Both domains ship an escape value: capture_source has "Unknown", and conflict has "Other". name is globally UNIQUE across both categories, so a capture_source tag cannot share a name with a free tag.

`event_tags`¶

Many-to-many junction table between events and tags.

Column	Type	Constraints
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE
`tag_id`	`UUID`	FK → `tags.id` ON DELETE CASCADE

Composite PK: (event_id, tag_id)

`conflicts`¶

The conflict referential: one row per armed conflict, externally synced. Three writers feed it, discriminated by source: the daily Wikipedia ongoing-conflicts sync (sync), the one-shot Wikidata historical seed (seed), and operator rows (manual), which include the Other escape value. See conflicts.md for the sync mechanics.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`name`	`VARCHAR(200)`	UNIQUE, NOT NULL. 200 characters, longer than the tags table's 100, because Wikipedia page names run long.
`wikidata_id`	`VARCHAR(20)`	UNIQUE, nullable. The Wikidata item id, for example `Q131569`. The natural key that the sync and seed writers upsert on. NULL on `manual` rows.
`start_year`	`INTEGER`	nullable. The sync fills it only where it is NULL (`conflicts.md`).
`end_year`	`INTEGER`	nullable
`ongoing`	`BOOLEAN`	NOT NULL, default `false`. Mirrors presence on the Wikipedia ongoing-conflicts page, with a 14-day grace period. No row is ever deleted.
`tier`	`VARCHAR(10)`	nullable. `'major'`, `'minor'`, or `'conflict'`: the Wikipedia death-toll tier table the sync last saw the row in (`conflicts.md` lists the bands). NULL for rows the sync has never seen.
`last_seen_at`	`TIMESTAMPTZ`	nullable. The last time the sync saw the row on the page. NULL for rows the sync has never seen: `manual` rows and never-listed `seed` rows. Those rows are immune to the grace-period deactivation.
`source`	`VARCHAR(20)`	NOT NULL, `'sync'`, `'seed'`, or `'manual'`

The sync upserts by wikidata_id, so a rename updates name in place. Mechanics: conflicts.md.

`event_conflicts`¶

Many-to-many junction table between events and conflicts, same shape as event_tags.

Column	Type	Constraints
`event_id`	`UUID`	FK → `events.id` ON DELETE CASCADE
`conflict_id`	`UUID`	FK → `conflicts.id` ON DELETE CASCADE

Composite PK: (event_id, conflict_id)

`bot_mentions`¶

The bot's idempotency ledger: one row per processed @-mention of the bot, whatever the outcome, so a mention is processed, and billed, at most once. The webhook and the reconciliation poll share it. See ingestion.md for the pipeline.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`mention_tweet_id`	`VARCHAR(25)`	UNIQUE, NOT NULL. The tagged tweet's id: an X snowflake, stored as a numeric string.
`author_handle`	`VARCHAR(50)`	NOT NULL. The tagging analyst's handle, normalized to lowercase with no leading `@`. Stored for forensics, not as a FK. Attribution resolves through the admin-linked `users.x_handle`.
`outcome`	`VARCHAR(20)`	NOT NULL, `'created'`, `'updated'` (no row created, an open detection overwritten with the newer parse, which earns the success reply as a creation does), `'requested'` (no coordinate, so a `requested` row was opened over the thread's footage instead, which earns its own success reply; see `ingestion.md`), `'inherited'` (the author never typed the tag, X's reply prefix carried it over from the parent, so nothing was acquired and nothing answered), `'no_detection'`, `'no_account'` (no live account carries the tagged author's admin-linked `x_handle`, so nothing is created and no reply is sent), `'skipped'` (a match the pass moved nothing on), `'self'` (the bot's own post, ledgered so the cursor advances past it), or `'failed'`. A `failed` row retries only when an operator deletes it.
`events_created`	`INTEGER`	NOT NULL, default 0. Detections only: a mention that opened a request counts 0 here and is told apart by its `outcome`.
`reply_tweet_id`	`VARCHAR(25)`	nullable. The bot's in-thread reply: on success, an event reference plus warnings; on failure, the refusal the engine named, sent only to linked authors (see `ingestion.md`). NULL when no reply was earned, reply credentials are absent, or the post failed. The detection stays durable either way.
`processed_at`	`TIMESTAMPTZ`	NOT NULL

`bot_webhook_events`¶

The queue between the X Account Activity webhook endpoint (POST /webhooks/x), which must answer X fast and therefore only inserts, and the import worker, which drains the queue through the shared mention pipeline. Idempotency lives in bot_mentions, not here: a redelivered or poll-raced mention processes once. See ingestion.md.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`mention`	`JSONB`	NOT NULL. The internal `Mention` shape: `tweet_id`, `author_id`, `author_handle`, `text`, `in_reply_to_user_id`, `in_reply_to_status_id`. Carries everything the pipeline needs, so a drain never re-reads, and never re-bills, the paid API.
`status`	`VARCHAR(10)`	NOT NULL. `'queued'` → `'processing'` → `'done'` \| `'failed'`. `processing` marks a claimed row so a concurrent worker skips it. An exception re-queues the row; a hard worker crash strands it, and the reconciliation poll re-delivers the mention. `done` means the pipeline ran; the per-mention outcome, including a ledgered `failed`, lives in `bot_mentions`. `failed` means the attempt budget is spent or the payload was malformed. A composite index on `(status, created_at)` matches the claim query.
`attempts`	`INTEGER`	NOT NULL, default 0. A claim counter. When it reaches the budget, the row lands `failed`, a poison-pill guard.
`created_at`	`TIMESTAMPTZ`	NOT NULL

`archive_import_jobs`¶

The durable queue behind POST /events/import-archive. The endpoint stages the uploaded zip to storage and inserts a row. The worker service claims rows with FOR UPDATE SKIP LOCKED, runs the backfill, stamps the import counts, and emails the owner. See ingestion.md for the pipeline and recovery semantics.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`owner_id`	`UUID`	FK → `users.id`, ON DELETE CASCADE, NOT NULL, indexed. The uploader. Every resulting row lands `detected` under this owner.
`zip_key`	`TEXT`	NOT NULL. The storage key of the staged upload (`archive-imports/<id>.zip`). The object is deleted when the job reaches a terminal state.
`status`	`VARCHAR(10)`	NOT NULL, indexed. `'queued'` → `'running'` → `'done'` \| `'failed'`. A `running` row whose `started_at` is past the stale window is reclaimable, because the worker died mid-job.
`attempts`	`INTEGER`	NOT NULL, default 0. A claim counter. When it reaches the budget, the job lands `failed` instead of looping, a poison-pill guard.
`post_estimate`	`INTEGER`	nullable. A volume hint from zip metadata, stamped at enqueue: the declared `tweets.js` size divided by a per-record average. Display only.
`progress_done` / `progress_total`	`INTEGER`	NOT NULL default 0, and nullable, respectively. The worker's live scan position, updated every few rows once the parse has the exact detection count.
`created_count` / `updated_count` / `skipped_count` / `failed_count`	`INTEGER`	NOT NULL, default 0. The import counts, final once `done`, disjoint.
`error`	`TEXT`	nullable. A terse, operator-facing failure reason. The owner gets the full story by email.
`created_at`	`TIMESTAMPTZ`	NOT NULL
`started_at` / `finished_at`	`TIMESTAMPTZ`	nullable

`source_archives`¶

One row per link an analyst has recorded an archived copy for: the event's source_url, its event_source_links mirrors, its detected_from_url, or an http(s) href in the proof body's Tiptap document. A row exists because a copy exists, so there is no queue state and no attempt counter. The capture happens in the analyst's own browser, and the write forms are where the snapshot URL comes back: source_snapshot_url, secondary_snapshot_urls and, on POST /events/{id}/versions, detected_from_snapshot_url. See archival.md for the flow and the validation.

Column	Type	Constraints
`id`	`UUID`	PK, default `uuid4()`
`event_id`	`UUID`	FK → `events.id`, ON DELETE CASCADE, NOT NULL, indexed
`original_url`	`TEXT`	NOT NULL. The link exactly as stored on the event. It is never normalized, because it is half the row's identity and what the read surface matches against `events.source_url`.
`origin`	`VARCHAR(20)`	NOT NULL, `ck_source_archives_origin_valid`: `'source_url'` (the event's declared footage source), `'secondary_source'` (a row of `event_source_links`), `'detected_from'` (the event's `detected_from_url`, the post a machine detection came from), or `'proof_link'` (an href inside the proof body). A link reachable from several of these is stored once, under the first of them it appears in.
`snapshot_url`	`TEXT`	NOT NULL. The archived copy, checked for provider and path shape before it is stored (see `archival.md`).
`provider`	`VARCHAR(20)`	NOT NULL, `ck_source_archives_provider_valid`: `'wayback'`, `'archive_today'` or `'ghostarchive'`. Inferred from the snapshot's host at write time, and what the read surface names the copy from.
`created_at`	`TIMESTAMPTZ`	NOT NULL

UNIQUE (event_id, original_url) is one copy per link. It is also the owner's correction path: pasting a second snapshot for a link overwrites the row rather than adding a competing one.

The table holds the copies as they stand. On a geolocated event, the edit that writes one also files an event_versions row, whose archives fragment is what the copies were at that version, so the history reads them back after a correction overwrites the live row.

Design decisions¶

Why JSONB for `proof`?¶

Tiptap, the rich editor, serializes content as ProseMirror JSON. Storing that JSON as-is in a JSONB column avoids conversion. PostgreSQL indexes and queries JSONB natively.

Why `event_tags` and not an array column on `events`?¶

This is a many-to-many relationship: a geolocation can carry several tags, and a tag can appear on many geolocations. The event_tags junction table is the standard solution. It supports efficient filtering (WHERE tag_id = X) and indexing on both sides. The alternative, a tag_ids[] array on events, would make filters more complex and less performant at scale.

Why a single `tags` table with a category?¶

Capture-source and free-form tags share the same mechanics: filtering and many-to-many association. A single table plus a category field avoids duplicating the logic. The distinction stays queryable: WHERE category = 'capture_source'.

Why `GEOMETRY` instead of two `lat` / `lng` columns?¶

PostGIS enables native geospatial queries: bounding-box filtering, distance computation, and clustering. GeoAlchemy2 exposes those types directly to SQLAlchemy.

Why a `event_geolocators` table and not an id array?¶

The table is read from both sides: an event's geolocators, and a user's geolocations on the profile page. A junction table indexes both directions and carries a per-row created_at. An id array on events would force a GIN scan for the reverse query and store no timestamp.

Why full snapshots in `event_versions` and not a per-field change log?¶

A version has to be readable on its own: /events/{id}/v2 renders the whole event as it stood, which a change log answers only by replaying every entry from the beginning. A snapshot answers it with one row, and the fields it stores are exactly the fields an edit can write, so a snapshot plus the live row is a complete history. The cost is bounded: an edit that changes nothing files nothing, and an event stops at 100 versions. What changed between two versions is a diff of adjacent snapshots, computed at read time.

Data model¶

Schema overview¶

Tables¶

users¶

auth_tokens¶

invite_codes¶

pending_registrations¶

auth_events¶

admin_events¶

events¶

event_geolocators¶

event_versions¶

event_source_links¶

content_reports¶

follows¶

collections¶

collection_events¶

media¶

tags¶

event_tags¶

conflicts¶

event_conflicts¶

bot_mentions¶

bot_webhook_events¶

archive_import_jobs¶

source_archives¶

Design decisions¶

Why JSONB for proof?¶

Why event_tags and not an array column on events?¶

Why a single tags table with a category?¶

Why GEOMETRY instead of two lat / lng columns?¶

Why a event_geolocators table and not an id array?¶

Why full snapshots in event_versions and not a per-field change log?¶

`users`¶

`auth_tokens`¶

`invite_codes`¶

`pending_registrations`¶

`auth_events`¶

`admin_events`¶

`events`¶

`event_geolocators`¶

`event_versions`¶

`event_source_links`¶

`content_reports`¶

`follows`¶

`collections`¶

`collection_events`¶

`media`¶

`tags`¶

`event_tags`¶

`conflicts`¶

`event_conflicts`¶

`bot_mentions`¶

`bot_webhook_events`¶

`archive_import_jobs`¶

`source_archives`¶

Why JSONB for `proof`?¶

Why `event_tags` and not an array column on `events`?¶

Why a single `tags` table with a category?¶

Why `GEOMETRY` instead of two `lat` / `lng` columns?¶

Why a `event_geolocators` table and not an id array?¶

Why full snapshots in `event_versions` and not a per-field change log?¶