pal-e-docs

Ticket: Implement streamlit_admin.py — 9 pages + Keycloak OIDC

ticket-westside-ops-streamlit-app Doc

active backlog westside-ops ticket
Project

Ticket: Implement streamlit_admin.py

Story: story-westside-ops-spreadsheet-access
Architecture: arch-domain-westside-ops (query shapes), arch-dataflow-westside-ops (OIDC + DB flow)
Labels: story:spreadsheet-access,arch:streamlit-app,type:feature,track:backend,scope:planned
Blocks: Woodpecker pipeline (needs real app code to build)
Blocked by: repo bootstrap, Postgres role

Purpose

Replace the stub streamlit_admin.py with the real application: Keycloak OIDC auth, a cached Postgres connection to basketball-api using the westside_ops_reader role, and 9 pages — one per data surface per arch-domain-westside-ops. Each page runs a raw SQL query and renders the result with st.data_editor(df, disabled=True, use_container_width=True, hide_index=True).

Scope

Auth layer (top of file, runs first)

  • Use streamlit-keycloak (or equivalent OIDC library — verify license and maintenance signal before picking)
  • Read Keycloak URL, realm, client ID, client secret from environment variables
  • Enforce login: if !authenticated, show only the Keycloak login widget and stop execution
  • Read the authenticated user's ID token, extract sub claim and realm_access.roles
  • Require the westside-ops-user role to be present; otherwise show "access denied" and stop
  • Display the user's email/name in the sidebar footer for clarity

Connection layer

  • @st.cache_resource decorated function that returns a psycopg2 connection to postgres.basketball-api.svc.cluster.local:5432
  • Connection string from env: WESTSIDE_OPS_DATABASE_URL (format: postgresql://westside_ops_reader:PASSWORD@postgres.basketball-api.svc.cluster.local:5432/basketball)
  • Helper run_query(sql, params=None) -> pd.DataFrame that reuses the cached connection

Page layer (9 pages, one function each)

Sidebar st.sidebar.selectbox selects the page. Each page function runs its SQL, renders the grid, and shows a row count. Queries per arch-domain-westside-ops Components table:
# Page Query shape
1 <strong>Players</strong> <code>SELECT p.id, p.name, p.division, p.jersey_order_status, p.contract_status, p.subscription_status, p.monthly_fee, pr.email, pr.phone, pr.waiver_signed, string_agg(t.name, ', ') AS teams FROM players p LEFT JOIN parents pr ON p.parent_id=pr.id LEFT JOIN player_teams pt ON pt.player_id=p.id LEFT JOIN teams t ON t.id=pt.team_id WHERE p.tenant_id=1 GROUP BY p.id, pr.email, pr.phone, pr.waiver_signed ORDER BY p.name;</code>
2 <strong>Parents</strong> <code>SELECT pr.id, pr.name, pr.email, pr.phone, pr.waiver_signed, pr.waiver_signed_at, COUNT(p.id) AS player_count, string_agg(p.name, ', ') AS players FROM parents pr LEFT JOIN players p ON p.parent_id=pr.id WHERE pr.tenant_id=1 GROUP BY pr.id ORDER BY pr.name;</code>
3 <strong>Teams &amp; Rosters</strong> <code>SELECT t.id, t.name, t.division, t.age_group, c.name AS coach, COUNT(pt.player_id) AS roster_size, string_agg(p.name, ', ') AS players FROM teams t LEFT JOIN coaches c ON c.id=t.coach_id LEFT JOIN player_teams pt ON pt.team_id=t.id LEFT JOIN players p ON p.id=pt.player_id WHERE t.tenant_id=1 GROUP BY t.id, c.name ORDER BY t.division, t.name;</code>
4 <strong>Contracts</strong> <code>SELECT p.id, p.name, p.division, p.contract_status, p.contract_signed_at, p.contract_signed_by, p.monthly_fee, pr.email, pr.phone FROM players p LEFT JOIN parents pr ON p.parent_id=pr.id WHERE p.tenant_id=1 ORDER BY p.contract_status, p.name;</code>
5 <strong>Jerseys &amp; Orders</strong> <code>SELECT o.id, o.created_at, p.name AS player, p.division, prod.name AS product, o.amount_cents, o.status AS order_status, p.jersey_option, p.jersey_size, p.jersey_number, p.jersey_order_status FROM orders o JOIN players p ON o.player_id=p.id JOIN products prod ON o.product_id=prod.id WHERE o.tenant_id=1 ORDER BY o.created_at DESC;</code>
6 <strong>Email Log</strong> <code>SELECT el.sent_at, el.email_type, el.recipient_email, p.name AS player, pr.name AS parent, el.gmail_message_id FROM email_log el LEFT JOIN parents pr ON el.parent_id=pr.id LEFT JOIN players p ON el.player_id=p.id WHERE el.tenant_id=1 ORDER BY el.sent_at DESC LIMIT 1000;</code>
7 <strong>Schedule</strong> Two queries rendered as two grids: events (<code>SELECT e.start_date, e.end_date, e.event_type, e.title, e.division, t.name AS team, e.location, e.opponent FROM events e LEFT JOIN teams t ON e.team_id=t.id WHERE e.tenant_id=1 ORDER BY e.start_date DESC;</code>) and practice_schedules (<code>SELECT ps.day_of_week, ps.start_time, ps.end_time, ps.label, ps.division, t.name AS team, ps.location, ps.is_active FROM practice_schedules ps LEFT JOIN teams t ON ps.team_id=t.id WHERE ps.tenant_id=1 ORDER BY ps.day_of_week, ps.start_time;</code>)
8 <strong>Coaches</strong> <code>SELECT c.id, c.name, c.email, c.phone, c.role, c.onboarding_status, c.contractor_agreement_signed, c.stripe_connect_account_id IS NOT NULL AS stripe_connected, COUNT(t.id) AS teams_coached FROM coaches c LEFT JOIN teams t ON t.coach_id=c.id WHERE c.tenant_id=1 GROUP BY c.id ORDER BY c.name;</code>
9 <strong>Sponsors</strong> <code>SELECT * FROM sponsors ORDER BY id DESC;</code> — schema drift finding from arch-domain-westside-ops means the exact columns need verification at implementation time via <code>\d sponsors</code> in the live DB

Rendering each page

  • df = run_query(SQL)
  • st.caption(f"{len(df)} rows")
  • st.data_editor(df, disabled=True, use_container_width=True, hide_index=True, num_rows="fixed")

Environment variables (documented in README)

  • WESTSIDE_OPS_DATABASE_URL — Postgres connection string as westside_ops_reader
  • KEYCLOAK_URL — http://keycloak.keycloak.svc.cluster.local
  • KEYCLOAK_REALM — westside
  • KEYCLOAK_CLIENT_ID — westside-ops
  • KEYCLOAK_CLIENT_SECRET — from SOPS secret
  • STREAMLIT_REQUIRED_ROLE — westside-ops-user (default)

Acceptance Criteria

  • [ ] Running streamlit run streamlit_admin.py locally with env vars set shows the Keycloak login widget
  • [ ] After logging in with a test account that has westside-ops-user role, the sidebar shows 9 pages
  • [ ] Each of the 9 pages loads its grid without error and displays the expected data from basketball-api
  • [ ] Every grid supports sort, filter, search, and copy via st.data_editor defaults
  • [ ] Attempting to query oauth_tokens from within the app (via a debug query) returns permission denied — proves the Postgres role allowlist is enforced
  • [ ] A user without the westside-ops-user role sees "access denied" and cannot reach any page
  • [ ] Pod restart causes re-login (expected — session state is in memory) but data is unchanged
  • [ ] Code is <500 lines total (complexity budget — if it grows past this, we've added scope we shouldn't have)

Files touched

  • ~/westside-ops/streamlit_admin.py (major)
  • ~/westside-ops/requirements.txt (pin OIDC library once chosen)
  • ~/westside-ops/README.md (environment variables section)

Rollback

Revert the commit. Pod is serving the stub — no cluster impact.

Out of scope

  • Write access / edit mode — v1 is read-only
  • Action buttons ("email all Kings") — Marcus uses copy-paste for v1
  • Saved views, bookmarks, URL query params — v1 relies on in-grid filtering
  • Row-level security per-user — v1 is operator-level, all users see all data
  • Per-page custom styling — Streamlit defaults only
  • Test coverage — for a UI that's a thin wrapper over SQL, visual verification is the test. If complexity grows, tests come with that growth.

Dependencies

Blocked by: repo bootstrap (T3), Postgres role (T2). Can proceed as soon as both are complete.