pal-e-docs
Project not found.

Domain Model: westside-ops

arch-domain-westside-ops Architecture

architecture active westside-ops
Project

Domain Model: westside-ops

westside-ops does not own a schema — it reads a curated subset of basketball-api's Postgres tables through the westside_ops_reader role. This note documents which tables are exposed, how they group into the 9 Streamlit pages, and what is explicitly blocked.

Diagram

Components

Exposed tables (14 total) — grouped by Streamlit page

Streamlit Page Primary Table Joined Tables Purpose
<strong>Players</strong> <code>players</code> <code>parents</code>, <code>teams</code> via <code>player_teams</code> Full roster with contact info, jersey status, contract status, team assignment. Marcus's #1 daily view.
<strong>Parents</strong> <code>parents</code> <code>players</code> (aggregated count) Parent contact list, waiver status, phone completeness. For collecting missing info.
<strong>Teams &amp; Rosters</strong> <code>teams</code> <code>player_teams</code>, <code>players</code>, <code>coaches</code> Per-team rosters (5 Kings teams, 2 Queens teams). Team assignment overview.
<strong>Contracts</strong> <code>players</code> (contract fields) <code>parents</code> Contract lifecycle: none → offered → signed / declined. Marcus's chase list.
<strong>Jerseys &amp; Orders</strong> <code>orders</code> <code>products</code>, <code>players</code>, <code>parents</code> All purchases (jerseys, contract fees, tournaments). Status, payment, fulfillment.
<strong>Email Log</strong> <code>email_log</code> <code>parents</code>, <code>players</code> Every email sent, by type, recipient, date. Transparency + audit.
<strong>Schedule</strong> <code>events</code> + <code>practice_schedules</code> <code>teams</code> Tournaments, games, camps, tryouts, recurring practices. Per-team filtering.
<strong>Coaches</strong> <code>coaches</code> <code>teams</code> Coach onboarding status, contractor agreement, contact info.
<strong>Sponsors</strong> <code>sponsors</code> — Sponsor outreach status (44 sponsor_outreach emails already sent per email_log). <strong>Schema drift:</strong> this table exists in live DB but is not in basketball-api's <code>models.py</code>. See Key Decisions.
Additional exposed tables used in joins or reference views: tenants (for future multi-tenant filtering), registrations (for tryout payment history), products (for order pricing reference), interest_leads (for the public form submissions admin view), player_teams (junction).

Forbidden tables (3 — never exposed)

Table Why blocked
<code>oauth_tokens</code> <strong>Credential store.</strong> JSONB column contains Gmail access tokens and refresh tokens for <code>westsidebasketball@gmail.com</code>. Exposing this = full account compromise. Blocked at the Postgres role level, not just hidden in UI.
<code>password_reset_tokens</code> Auth secrets. Short-lived but still privileged. No operator use case.
<code>outbox</code> Internal event queue. Not data Marcus needs; exposing it would invite confusion and accidental edits to in-flight events.

The westside_ops_reader role

Key Decisions

  • Schema subset, not schema ownership. westside-ops reads basketball-api's tables directly via a read-only role. No ORM, no migrations, no shared models.py import. The Streamlit app issues raw SQL via psycopg2. This means the tool survives basketball-api schema changes without coordination — if basketball-api adds a column to players, the Streamlit SELECT * picks it up on the next page load; if a column is renamed, the specific query mentioning it breaks visibly in one place.
  • 14 tables in, 3 tables out. The allowlist is explicit and narrow. Every exposed table has a clear Marcus-workflow justification in the Components table. The forbidden list contains only tables with production secrets or internal state. New basketball-api tables are not automatically exposed — adding a table to westside-ops requires a deliberate GRANT addition and a ticket.
  • Grouping into 9 pages, not 60 views. Marcus's need is not "60 named saved views" — it's "a few pages with powerful in-grid filtering." Each Streamlit page shows the full dataset for a concern (all players, all parents, all contracts) and Marcus filters within the grid using st.data_editor's built-in column filters. One "Contracts" page covers "unsigned contracts," "declined contracts," "Kings with offered contracts," and every other contract cohort Marcus might need — all via runtime filtering, zero developer involvement.
  • Schema drift finding: sponsors table + sponsor_outreach email type exist in live DB but not in basketball-api/src/basketball_api/models.py. Discovered during the scoping audit. westside-ops includes sponsors in its GRANT allowlist because the live workflow is active (44 sponsor_outreach emails sent per email_log). This is discovered scope for a separate basketball-api ticket — models.py should be brought back into sync with production, but that's not westside-ops's work to do.
  • tenant_id is always in the WHERE clause. Every query filters by tenant_id = 1 (Westside Kings & Queens) even though there's only one tenant today. This future-proofs the tool against the day a second tenant joins and prevents accidentally showing another org's data if multi-tenant goes live.
  • No read-your-writes consistency problem. Because westside-ops is read-only and the data source is the same Postgres cluster that basketball-api writes to, Marcus sees changes immediately after any basketball-api workflow completes. No cache, no sync, no staleness. The only lag is page refresh.
  • arch-deployment-westside-ops — how this subset is served at runtime
  • arch-dataflow-westside-ops — the query flow from Marcus's click to a rendered grid
  • story-westside-ops-spreadsheet-access — the user story this domain model serves
  • nocodb-basketball-api-scoping (archived) — original schema audit, GRANT allowlist derivation, and NocoDB evaluation
  • basketball-api models: ~/basketball-api/src/basketball_api/models.py (source of truth for 17 of the 18 live tables)