Skip to content

Data model

Core ORM models live under src/lib/db/models/. This page outlines relationships and delete behaviour. Edges labeled CASCADE or SET NULL have explicit Postgres ON DELETE actions; unlabeled foreign keys use the default NO ACTION (restrict).

Relationships

erDiagram
  League ||--o{ Season : has
  League ||--o{ Team : has
  League ||--o{ Match : has
  League ||--o{ QuestionDefinition : has
  Season ||--o{ Match : has
  Team ||--o{ Match : home_or_away
  Brand ||--o{ Match : "sponsors match"
  Brand ||--o{ QuestionSponsorship : "sponsors slot"
  Brand ||--o{ QuestionIssuance : "stamped sponsor"
  User ||--o{ Match : "created_by SET NULL"
  User ||--o{ QuestionAnswer : answers
  User ||--o{ MatchFollow : follows
  User ||--o{ PushDevice : "CASCADE"
  User ||--o{ PushDeliveryAttempt : "CASCADE"
  Match ||--o| Match : "cloned_from SET NULL"
  Match ||--o{ QuestionIssuance : "CASCADE"
  Match ||--o{ QuestionSponsorship : "CASCADE"
  Match ||--o{ MatchListenerRecord : "CASCADE"
  Match ||--o{ MatchFollow : "CASCADE"
  Match ||--o{ PushDeliveryAttempt : "CASCADE"
  QuestionDefinition ||--o{ QuestionIssuance : defines
  QuestionDefinition ||--o{ QuestionSponsorship : "optional pin"
  QuestionSponsorship ||--o{ QuestionIssuance : "slot SET NULL"
  QuestionIssuance ||--o{ QuestionAnswer : "CASCADE"
  PushDevice ||--o{ PushDeliveryAttempt : "SET NULL"

push_devices stores push tokens (Expo today; APNs/FCM reserved) keyed by client device_id. push_delivery_attempts is a short-lived triage log (default 7-day prune). question_issuance_id on that table is the Play issuance id (in-game or Match question) and is not a foreign key. Operator push gates/templates live in stored_settings key push_notification_settings. Per-user opt-in lives on users.notify_on_issuance and users.notify_on_resolve (default true).

Match delete behaviour

Only is_test matches may be deleted (non-main env). Ops stop the listener first; Postgres removes owned children and nulls clone backrefs.

flowchart TD
  start[DELETE /api/matches/id] --> gate{is_test and env allows?}
  gate -->|no| reject[400 or 404]
  gate -->|yes| stop[stop_match_listener Celery Redis]
  stop --> delMatch[ORM DELETE match]
  delMatch --> db{Postgres ON DELETE}
  db --> c1[CASCADE question_issuances]
  db --> c1b[CASCADE question_issuance_denials]
  db --> c2[CASCADE question_sponsorships]
  db --> c3[CASCADE match_listeners]
  db --> c4[SET NULL clones.cloned_from_match_id]
  c1 --> answers[CASCADE question_answers]
  c2 --> stamp[SET NULL issuance.question_sponsorship_id]

Implementation: delete_test_match in src/lib/matches/ops.py. FK policy for new models: .cursor/rules/database.mdc (Foreign key delete actions).