Westside Ops

Ticket: Marcus Tailscale onboarding + first login walkthrough

Story: story-westside-ops-spreadsheet-access (this ticket fulfills the Success Metric)
Architecture: arch-deployment-westside-ops
Labels: story:spreadsheet-access,arch:deployment,type:onboarding,track:ops,scope:planned
Blocks: nothing (this is the final ticket)
Blocked by: k8s overlay (needs a live URL)

Purpose

Get Marcus's phone onto the tailnet, confirm his Keycloak account has the westside-ops-user role, walk him through the first login, and verify end-to-end that the paradigm is delivered: Marcus independently sees his data without AI, developer, or admin in the loop. This ticket is where the story's Success Metric is validated.

Scope

Phase 1: Tailscale install on Marcus's phone

• Lucas sits with Marcus (in person or over a call) for ~10 minutes
• Marcus opens the App Store / Play Store and installs Tailscale
• Lucas opens the Tailscale admin console and generates an invite link for Marcus (email-based invite to the tailnet)
• Marcus opens the invite, signs in with his email provider, joins the tailnet
• Verify from Tailscale admin console that Marcus's phone appears as a tailnet member

Phase 2: Keycloak role assignment (may already be done by services-entry ticket)

• Open Keycloak admin console: https://keycloak.tail5b443a.ts.net
• Navigate to the westside realm → Users → find Marcus's account by email
• Go to Role Mappings → assign westside-ops-user realm role
• If the role doesn't exist yet, create it first in Realm Roles

Phase 3: First login walkthrough

• Marcus opens Safari/Chrome on his phone and navigates to https://westside-ops.tail5b443a.ts.net
• Verify: page loads (proves tailnet membership working), redirects to Keycloak (proves OIDC flow)
• Marcus signs in with his existing westside credentials
• After login: verify the sidebar shows all 9 pages
• Marcus clicks "Players" — verify the grid loads with 66 rows
• Walk Marcus through: (1) sorting by clicking a column header, (2) using the column filter icon, (3) selecting a cell range, (4) copying with Ctrl+C (or long-press on mobile), (5) pasting into another app
• Show Marcus the other 8 pages so he knows what's there
• Answer whatever questions come up — write them down if they suggest feature gaps

Phase 4: 1-week observation window (the Success Metric)

• Marcus uses the tool for his actual weekly operational tasks without prompting from Lucas
• Lucas tracks: did Marcus's Westside-related messages to Lucas drop? Did Marcus complete at least 5 independent operational tasks using westside-ops?
• At end of week 1, Marcus and Lucas review: what worked, what didn't, what's missing
• Findings become new backlog tickets (probably: specific new page columns, specific filters that Marcus wants pre-applied, maybe a first action button if copy-paste friction shows up)

Acceptance Criteria

• [ ] Marcus's phone is on the tailnet (visible in Tailscale admin console)
• [ ] Marcus's Keycloak account has westside-ops-user role in the westside realm
• [ ] Marcus can load https://westside-ops.tail5b443a.ts.net on his phone without assistance
• [ ] Marcus can log in via Keycloak and see the sidebar with 9 pages
• [ ] Marcus can open the Players page and see 66 rows of real data
• [ ] Marcus demonstrates sort, filter, search, and copy independently (Lucas confirms by watching him do each once)
• [ ] After 1 week: Marcus has completed at least 5 operational tasks using the tool without asking Lucas/Ava to query data
• [ ] Week-1 retro captured as new tickets on board-westside-ops (backlog column, per backlog-first enforcement)

Files touched

• Tailscale admin console state (Marcus added to tailnet)
• Keycloak realm state (role assignment)
• No repo files

Rollback

Remove Marcus from the tailnet and/or revoke the westside-ops-user role. Marcus retains access to the existing westside-app — no disruption to his current workflows.

Out of scope

• Training Marcus on SQL, database concepts, or internal terminology. Show him what he can do, not how it works under the hood.
• Linking westside-ops from westside-app's existing admin — that's Phase 2 of the rollout plan, decided after Marcus has used westside-ops
• Documentation for Marcus. Plain-language per feedback_marcus_plain_language: one short "how to use this" paragraph is enough. If he needs more, the tool is wrong.
• Adding other Westside staff or coaches. v1 is Marcus only. Other users are a follow-up story.

Dependencies

Blocked by: everything above. This is the last ticket in the chain.

Ticket: k8s overlay — Deployment, Service, private Tailscale ingress, SOPS secrets

Story: story-westside-ops-spreadsheet-access
Architecture: arch-deployment-westside-ops
Labels: story:spreadsheet-access,arch:k8s-overlay,type:infra,track:devops,scope:planned
Blocks: Marcus onboarding (needs a live URL)
Blocked by: Postgres role, services-entry, Woodpecker pipeline (needs an image in Harbor)

Purpose

Write the k8s manifests inside ~/westside-ops/k8s/ that ArgoCD syncs: Deployment (pulling the Streamlit image from Harbor), Service, private Tailscale Ingress (tailscale.com/expose: "true" — not funnel), and SOPS-encrypted secrets. ArgoCD's Application resource already exists (created by services-entry) and is pointed at westside-ops repo's k8s path.

Scope

• Write 5 files under ~/westside-ops/k8s/: kustomization.yaml, deployment.yaml, service.yaml, ingress.yaml, secrets.enc.yaml (SOPS-encrypted)
• Use SOPS age encryption matching the existing overlays/basketball-api/prod/harbor-creds.enc.yaml pattern — same recipient, same sops config
• Populate secrets.enc.yaml with: WESTSIDE_OPS_DATABASE_URL, KEYCLOAK_CLIENT_SECRET, COOKIE_SECRET (random 32 bytes)
• Commit and push to Forgejo — ArgoCD will auto-sync within seconds
• Verify: pod starts, becomes ready, Ingress gets a Tailscale address, curl -k https://westside-ops.tail5b443a.ts.net returns a Streamlit HTML page (from within the tailnet)

Exact manifests

kustomization.yaml

apiVersion: kustomize.config.k8s.io/v1beta1
kind: Kustomization
resources:
  - deployment.yaml
  - service.yaml
  - ingress.yaml
  - secrets.enc.yaml
images:
  - name: app-image
    newName: harbor.tail5b443a.ts.net/westside-ops/app
    newTag: latest   # Image Updater will overwrite this with a commit SHA on first successful build

deployment.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: westside-ops
  labels:
    app: westside-ops
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: westside-ops
  template:
    metadata:
      labels:
        app: westside-ops
    spec:
      imagePullSecrets:
        - name: harbor-creds
      containers:
        - name: westside-ops
          image: app-image
          ports:
            - containerPort: 8501
              name: http
          env:
            - name: KEYCLOAK_URL
              value: "http://keycloak.keycloak.svc.cluster.local"
            - name: KEYCLOAK_REALM
              value: "westside"
            - name: KEYCLOAK_CLIENT_ID
              value: "westside-ops"
            - name: STREAMLIT_REQUIRED_ROLE
              value: "westside-ops-user"
            - name: WESTSIDE_OPS_DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: westside-ops-secrets
                  key: database-url
            - name: KEYCLOAK_CLIENT_SECRET
              valueFrom:
                secretKeyRef:
                  name: westside-ops-secrets
                  key: keycloak-client-secret
            - name: COOKIE_SECRET
              valueFrom:
                secretKeyRef:
                  name: westside-ops-secrets
                  key: cookie-secret
          readinessProbe:
            httpGet:
              path: /_stcore/health
              port: 8501
            initialDelaySeconds: 10
            periodSeconds: 10
          livenessProbe:
            httpGet:
              path: /_stcore/health
              port: 8501
            initialDelaySeconds: 30
            periodSeconds: 30
          resources:
            requests:
              cpu: 50m
              memory: 256Mi
            limits:
              memory: 512Mi

service.yaml

apiVersion: v1
kind: Service
metadata:
  name: westside-ops
  labels:
    app: westside-ops
spec:
  selector:
    app: westside-ops
  ports:
    - name: http
      port: 8501
      targetPort: 8501

ingress.yaml — the load-bearing file

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: westside-ops
  annotations:
    tailscale.com/expose: "true"   # PRIVATE — NOT funnel. Only tailnet members can reach this URL.
spec:
  ingressClassName: tailscale
  defaultBackend:
    service:
      name: westside-ops
      port:
        number: 8501
  tls:
    - hosts:
        - westside-ops

secrets.enc.yaml (plaintext, to be encrypted with sops -e)

apiVersion: v1
kind: Secret
metadata:
  name: westside-ops-secrets
type: Opaque
stringData:
  database-url: "postgresql://westside_ops_reader:PASSWORD_FROM_SECRETS_DIR@postgres.basketball-api.svc.cluster.local:5432/basketball"
  keycloak-client-secret: "FROM_KEYCLOAK_ADMIN_CONSOLE_AFTER_SERVICES_ENTRY_APPLIES"
  cookie-secret: "GENERATED_WITH_python_-c_secrets.token_urlsafe(32)"

After populating the plaintext, encrypt: sops -e -i secrets.enc.yaml. Verify with sops -d secrets.enc.yaml that it round-trips. Only the .enc.yaml version is committed.

Acceptance Criteria

• [ ] All 5 files exist in ~/westside-ops/k8s/, committed and pushed to Forgejo main
• [ ] secrets.enc.yaml is SOPS-encrypted (not plaintext); sops -d round-trips successfully
• [ ] ArgoCD application westside-ops reports Synced and Healthy
• [ ] kubectl get pod -n westside-ops shows the pod in Running status
• [ ] kubectl logs -n westside-ops deploy/westside-ops shows Streamlit bound to 0.0.0.0:8501
• [ ] kubectl get ingress -n westside-ops shows the ingress with a Tailscale address assigned
• [ ] curl -k https://westside-ops.tail5b443a.ts.net from a tailnet member returns a Streamlit HTML page (redirect to Keycloak login)
• [ ] curl -k https://westside-ops.tail5b443a.ts.net from a non-tailnet member returns an error (DNS resolution or connection refused) — confirms the private mode is working
• [ ] The pod's DB connection to basketball-api works (verify by logging in and loading the Players page — this moves into the Marcus onboarding ticket for end-to-end verification)

Files touched

• ~/westside-ops/k8s/kustomization.yaml
• ~/westside-ops/k8s/deployment.yaml
• ~/westside-ops/k8s/service.yaml
• ~/westside-ops/k8s/ingress.yaml
• ~/westside-ops/k8s/secrets.enc.yaml

Rollback

Revert the commit. ArgoCD syncs back to the empty state (the prior commit had k8s/.gitkeep only). Pod is torn down. Namespace and Harbor project persist (owned by services-entry). Full rollback requires services-entry rollback.

Out of scope

• NetworkPolicy — the existing bases/standard/networkpolicy.yaml is disabled per the kube-router ipset bug comment in existing overlays. Don't add a NetworkPolicy until that bug is fixed cluster-wide.
• HorizontalPodAutoscaler — single replica is fine for v1, Marcus is the only user
• ServiceMonitor / Prometheus scraping — Streamlit doesn't expose /metrics by default, and telemetry for Marcus-use isn't load-bearing. Follow-up ticket if needed.
• Cert-manager or any TLS setup — Tailscale operator handles TLS automatically

Dependencies

Blocked by: services-entry (namespace + harbor-creds secret), Postgres role (database URL password), streamlit-app implementation (the code), Woodpecker pipeline (the image in Harbor).

Ticket: pal-e-services — var.services entry + Keycloak client

Story: story-westside-ops-spreadsheet-access
Architecture: arch-deployment-westside-ops (pal-e-services as the tenancy layer)
Labels: story:spreadsheet-access,arch:services-tf,type:infra,track:devops,scope:planned
Blocks: Woodpecker pipeline (needs Harbor project + CI robot), k8s overlay (needs namespace + Keycloak client secret)
Blocked by: Streamlit spike ticket

Purpose

Register westside-ops as a service in pal-e-services so terraform provisions: Harbor project, CI robot, pull robot, namespace, harbor-creds image pull secret, ArgoCD application pointing at the westside-ops repo's k8s/ directory, and a Tailscale ingress (explicitly NOT a funnel). Also register a new Keycloak client westside-ops in the existing westside realm.

Scope

• Edit ~/pal-e-services/terraform/k3s.tfvars (the GPG-decrypted or local copy — do not commit)
• Add the service entry and Keycloak client below
• Run tofu plan -var-file=k3s.tfvars -lock=false and review for expected resources (Harbor project, 2 robots, namespace, secret, ArgoCD app, ingress, Keycloak client)
• Run tofu apply -var-file=k3s.tfvars -lock=false per feedback_tofu_lock_false
• Capture the CI robot credentials via tofu output and hand them to the Woodpecker pipeline ticket
• Capture the Keycloak client secret from the Keycloak admin console and hand it to the k8s overlay ticket (it will be SOPS-encrypted into secrets.enc.yaml)
• Add Marcus's Keycloak account to the new westside-ops-user role within the westside realm (manual step in Keycloak admin UI, or via mcp__pal-e-docs if Keycloak MCP exists)

Exact tfvars additions

Append to the services map in k3s.tfvars:

services = {
  # ... existing entries ...

  westside-ops = {
    forgejo_repo    = "forgejo_admin/westside-ops"
    source_repo     = "forgejo_admin/westside-ops"
    source_path     = "k8s"
    image_repo      = "westside-ops/app"
    port            = 8501
    funnel          = false   # CRITICAL: private Tailscale (tailscale.com/expose), NOT public funnel
    target_revision = "main"
  }
}

Append to the keycloak_clients map in k3s.tfvars:

keycloak_clients = {
  # ... existing entries ...

  westside-ops = {
    realm_key                   = "westside"   # reuse existing realm
    client_id                   = "westside-ops"
    name                        = "Westside Ops"
    public_client               = false        # confidential client; Streamlit holds the secret server-side
    standard_flow_enabled       = true
    pkce_code_challenge_method  = "S256"
    valid_redirect_uris         = ["https://westside-ops.tail5b443a.ts.net/*"]
    web_origins                 = ["https://westside-ops.tail5b443a.ts.net"]
    include_realm_roles_mapper  = true         # so Streamlit can read westside-ops-user role from the token
  }
}

Also needed: add westside-ops-user to the roles list of the existing westside realm definition in tfvars (if realms are declared there), or create the role manually in the Keycloak admin console if realms are managed outside tfvars.

Important: verify the funnel=false code path

Reading pal-e-services/terraform/services.tf: the current funnel resource is conditional on each.value.funnel being true and applies the tailscale.com/funnel: "true" annotation. For westside-ops, we need tailscale.com/expose: "true" instead — the private variant.

The current terraform module does not create an expose-style ingress when funnel=false — it just skips the ingress entirely. That means one of:

1. Option A: westside-ops manages its own ingress inside ~/westside-ops/k8s/ingress.yaml with the tailscale.com/expose annotation (the k8s overlay ticket handles this). funnel=false in tfvars just means "tofu doesn't create any ingress — the overlay does." This is the recommended path because it's consistent with how westside-landing / westsidekingsandqueens already manages its own ingress via its overlay.
2. Option B: Extend pal-e-services's services.tf to support a third mode (expose). More invasive, defer as a separate ticket.

Go with Option A. The ingress resource lives in ~/westside-ops/k8s/ingress.yaml, declared in the k8s overlay ticket.

Acceptance Criteria

• [ ] tofu plan -var-file=k3s.tfvars -lock=false shows expected new resources: harbor_project.service["westside-ops"], harbor_robot_account.service_ci["westside-ops"], harbor_robot_account.service_pull["westside-ops"], kubernetes_namespace_v1.service["westside-ops"], kubernetes_secret_v1.harbor_creds["westside-ops"], argocd_application.service["westside-ops"], and the new Keycloak client
• [ ] Plan does NOT show kubernetes_ingress_v1.service_funnel["westside-ops"] (funnel=false so the funnel resource is skipped)
• [ ] tofu apply -var-file=k3s.tfvars -lock=false succeeds
• [ ] CI robot credentials captured from tofu output ci_robot_usernames and tofu output -json ci_robot_passwords; handed to Woodpecker pipeline ticket
• [ ] Keycloak client secret captured from the admin console; encrypted into the k8s overlay's secrets.enc.yaml
• [ ] Marcus's Keycloak account has the westside-ops-user role assigned
• [ ] Namespace westside-ops exists in the cluster (kubectl get ns westside-ops)
• [ ] Harbor project westside-ops exists (verified in Harbor UI)
• [ ] ArgoCD application westside-ops exists but is OutOfSync (expected — the k8s overlay ticket hasn't committed manifests yet)

Files touched

• ~/pal-e-services/terraform/k3s.tfvars (local, not committed)
• Cluster state (Harbor project, 2 robots, namespace, harbor-creds secret, ArgoCD Application, Keycloak client, Keycloak role, Keycloak user→role assignment)

Rollback

Remove the westside-ops entry from services and keycloak_clients in tfvars. Run tofu apply -var-file=k3s.tfvars -lock=false. Terraform destroys everything it created. ArgoCD pruning may race with namespace deletion — transient errors are harmless per SERVICE_ONBOARDING.md.

Out of scope

• The westside_ops_reader Postgres role — separate ticket
• Application manifests (Deployment, Service, Ingress, Secrets) — k8s overlay ticket
• The Streamlit image build — Woodpecker pipeline ticket

Dependencies

Blocked by: Streamlit spike ticket. Can proceed in parallel with repo bootstrap and Postgres role.

Ticket: Woodpecker pipeline — build westside-ops to Harbor

Story: story-westside-ops-spreadsheet-access
Architecture: arch-deployment-westside-ops (Harbor-mirrored image decision)
Labels: story:spreadsheet-access,arch:woodpecker-ci,type:infra,track:devops,scope:planned
Blocks: k8s overlay (needs an image in Harbor to deploy)
Blocked by: repo bootstrap, streamlit_admin.py implementation, services-entry (needs Harbor project + CI robot credentials)

Purpose

Wire up the Woodpecker pipeline so every push to main builds the Streamlit image with Kaniko and pushes it to harbor.tail5b443a.ts.net/westside-ops/app:{CI_COMMIT_SHA}. Inherits the westsidekingsandqueens pattern exactly — single pipeline, Kaniko build, Harbor secrets, path exclude for Image Updater write-backs.

Scope

• Replace the skeleton .woodpecker.yaml from the repo bootstrap ticket with the real pipeline (see YAML below)
• Register the repo in Woodpecker UI at https://woodpecker.tail5b443a.ts.net by clicking "Add repository"
• Add two secrets to the Woodpecker repo settings:
  • harbor_username — from tofu output ci_robot_usernames after the services-entry ticket applies. Format: robot$westside-ops+westside-ops-ci
  • harbor_password — from tofu output -json ci_robot_passwords | jq -r '.["westside-ops"]'
• Push a trivial commit to trigger the first build; verify it succeeds and the image appears in Harbor

.woodpecker.yaml

when:
  - event: push
    branch: main
    path:
      exclude:
        - "k8s/.argocd-source-*"
  - event: pull_request

steps:
  - name: build-and-push
    image: woodpeckerci/plugin-kaniko:2.3.0
    settings:
      registry: harbor.tail5b443a.ts.net
      repo: westside-ops/app
      tags: $CI_COMMIT_SHA
      build_args:
        - BUILD_SHA=$CI_COMMIT_SHA
      username:
        from_secret: harbor_username
      password:
        from_secret: harbor_password
    when:
      - event: push
        branch: main

Gotchas (from existing services' lessons)

• Use $CI_COMMIT_SHA, NOT ${CI_COMMIT_SHA}. Curly braces break Woodpecker's compiler. Per SERVICE_ONBOARDING.md.
• Path exclude k8s/.argocd-source-* is mandatory. Without it, Image Updater write-backs trigger infinite build loops. Per feedback_ci_pipeline_lessons.
• Only two secrets needed. No ArgoCD deploy step — that's handled by the Image Updater + ArgoCD sync loop provisioned by pal-e-services.
• YAML parse validation. Per feedback_yaml_parse_validation, run python -c "import yaml; yaml.safe_load(open('.woodpecker.yaml'))" before committing to catch unquoted colons and similar gotchas.

Acceptance Criteria

• [ ] .woodpecker.yaml parses cleanly with yaml.safe_load
• [ ] Woodpecker repo registered at https://woodpecker.tail5b443a.ts.net, pipeline visible in UI
• [ ] harbor_username and harbor_password secrets set in Woodpecker (values from the services-entry ticket's tofu outputs)
• [ ] First build after push to main succeeds, visible in Woodpecker UI
• [ ] harbor.tail5b443a.ts.net/westside-ops/app:{commit-sha} exists and is pullable (verify in Harbor UI)
• [ ] Subsequent writeback commits from Image Updater do NOT trigger additional builds (path exclude working)

Files touched

• ~/westside-ops/.woodpecker.yaml (replace skeleton)
• Woodpecker UI state (repo registration, secrets)

Rollback

Delete the Woodpecker repo registration. Delete the Harbor project (tofu destroys it when services-entry is reverted). No cluster impact.

Out of scope

• Test step in the pipeline — v1 has no tests per the streamlit-app ticket's scope. Add a test step later if/when tests are added.
• Multi-arch builds — amd64 only for v1
• Semver tagging — commit SHA only, matching existing services

Dependencies

Blocked by: repo bootstrap, streamlit-app implementation, services-entry (for Harbor project + CI robot credentials).

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:

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.

Ticket: Bootstrap westside-ops Forgejo repo

Story: story-westside-ops-spreadsheet-access
Architecture: arch-deployment-westside-ops (sibling repo decision)
Labels: story:spreadsheet-access,arch:streamlit-app,type:infra,track:devops,scope:planned
Blocks: streamlit_admin.py implementation, Woodpecker pipeline, k8s overlay (all need the repo to exist)
Blocked by: Streamlit spike ticket

Purpose

Create the new Forgejo repo forgejo_admin/westside-ops and commit the skeleton: directory layout, Dockerfile, requirements.txt, empty streamlit_admin.py, skeleton .woodpecker.yaml, k8s/ directory, README. This ticket is pure scaffolding — no logic, no deployment. Downstream tickets fill in the pieces.

Scope

• Create Forgejo repo forgejo_admin/westside-ops via mcp__forgejo__create_repo (public: false, auto-init: false)
• Clone locally to ~/westside-ops/
• Commit the skeleton below on branch main
• Push to Forgejo
• Register the repo in Woodpecker UI (so the pipeline auto-triggers on push — happens in the pipeline ticket, not here)

Skeleton layout

westside-ops/
├── .gitignore                   # Python standard: __pycache__, .venv, .env, *.pyc
├── .woodpecker.yaml             # skeleton only; pipeline ticket fills it in
├── Dockerfile                   # python:3.12-slim + streamlit base
├── requirements.txt             # streamlit, pandas, psycopg2-binary, streamlit-keycloak (or equivalent)
├── streamlit_admin.py           # empty stub with one page: "Hello, westside-ops"
├── README.md                    # purpose, how to run locally, architecture note links
├── migrations/
│   └── 001-create-reader-role.sql   # copied from the Postgres role ticket for reference
└── k8s/
    └── .gitkeep                 # overlay ticket fills this in

Dockerfile

FROM python:3.12-slim AS build
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

FROM python:3.12-slim
WORKDIR /app
ARG BUILD_SHA=dev
ENV BUILD_SHA=${BUILD_SHA}
COPY --from=build /usr/local/lib/python3.12/site-packages /usr/local/lib/python3.12/site-packages
COPY --from=build /usr/local/bin/streamlit /usr/local/bin/streamlit
COPY streamlit_admin.py .
EXPOSE 8501
CMD ["streamlit", "run", "streamlit_admin.py", "--server.address=0.0.0.0", "--server.port=8501", "--server.headless=true"]

requirements.txt

streamlit>=1.40,<2
pandas>=2.2,<3
psycopg2-binary>=2.9,<3
# OIDC: exact package chosen in the streamlit-app implementation ticket
# streamlit-keycloak or streamlit-oauth — verify licensing and maintenance signal first

Stub streamlit_admin.py

import streamlit as st

st.set_page_config(page_title="Westside Ops", layout="wide")
st.title("Westside Ops")
st.caption("Operator data access for Westside Kings & Queens. See story-westside-ops-spreadsheet-access.")
st.info("Stub. Full implementation in ticket-westside-ops-streamlit-app.")

Acceptance Criteria

• [ ] Forgejo repo forgejo_admin/westside-ops exists (reachable via mcp__forgejo__get_repo)
• [ ] ~/westside-ops cloned locally with remote origin pointing at Forgejo
• [ ] The skeleton files above are all present and committed on main
• [ ] docker build -t westside-ops-local . succeeds locally (proves the Dockerfile works)
• [ ] docker run --rm -p 8501:8501 westside-ops-local serves the stub on localhost:8501
• [ ] README links to the three architecture notes and the user story note by slug

Files touched

All new files in a new repo. No other repos touched.

Rollback

Delete the Forgejo repo (via API or UI) and remove ~/westside-ops. No cluster state, no CI state.

Out of scope

• Real streamlit_admin.py logic — that's ticket-westside-ops-streamlit-app
• Real .woodpecker.yaml — that's ticket-westside-ops-woodpecker-pipeline
• k8s manifests — that's ticket-westside-ops-k8s-overlay
• Harbor project creation — that happens automatically when pal-e-services is applied in the services-entry ticket

Dependencies

Streamlit spike ticket must pass veto gate first.

Ticket: westside_ops_reader Postgres role + GRANT allowlist

Story: story-westside-ops-spreadsheet-access
Architecture: arch-domain-westside-ops (Key Decisions: defense-in-depth at role level)
Labels: story:spreadsheet-access,arch:postgres-role,type:infra,track:backend,scope:planned
Blocks: k8s overlay ticket (needs DB URL + password to populate the SOPS secret)
Blocked by: Streamlit spike ticket

Purpose

Create a dedicated Postgres role inside basketball-api's existing Postgres pod that is the only credential westside-ops ever uses to talk to the database. The role has explicit GRANTs on 14 tables and cannot read oauth_tokens, password_reset_tokens, or outbox. This is the load-bearing security control: even if the Streamlit app had a bug that tried to execute arbitrary SQL, those three tables would return permission denied.

basketball-api is hands-off per convention — this is the only touch we make, and it's an additive SQL migration run once via kubectl exec. No code changes to basketball-api.

Scope

• Generate a strong password for the new role (32+ random bytes). Store locally at ~/secrets/westside-ops/reader-db-password following the existing ~/secrets/{service}/ convention documented in ~/secrets/README.md
• Write a one-shot SQL file containing the CREATE ROLE + GRANT statements (see exact SQL below)
• Apply via kubectl exec -n basketball-api <postgres-pod> -- psql -U basketball -d basketball -f - (piping the SQL file in)
• Verify the allowlist works: connect as the new role and run a SELECT on every allowed table; confirm it succeeds
• Verify the blocklist works: connect as the new role and attempt SELECT * FROM oauth_tokens, SELECT * FROM password_reset_tokens, SELECT * FROM outbox; confirm all three return permission denied

Exact SQL

-- westside-ops Postgres role setup
-- Run ONCE against basketball-api's postgres pod as the 'basketball' role
-- basketball-api remains hands-off beyond this migration

\set reader_password `cat ~/secrets/westside-ops/reader-db-password`

-- Idempotency: only create the role if it doesn't already exist
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'westside_ops_reader') THEN
    CREATE ROLE westside_ops_reader LOGIN PASSWORD :'reader_password';
  END IF;
END$$;

GRANT CONNECT ON DATABASE basketball TO westside_ops_reader;
GRANT USAGE ON SCHEMA public TO westside_ops_reader;

-- Explicit allowlist — 14 tables
GRANT SELECT ON
  tenants,
  parents,
  players,
  player_teams,
  teams,
  coaches,
  registrations,
  orders,
  products,
  email_log,
  practice_schedules,
  events,
  interest_leads,
  sponsors
TO westside_ops_reader;

-- NOTE: the following are intentionally NOT granted and default to no access:
--   oauth_tokens         (contains Gmail refresh tokens — credential disclosure risk)
--   password_reset_tokens (auth secrets)
--   outbox                (internal event queue)
--   alembic_version       (migration metadata)

-- Revoke everything on the forbidden tables just to be explicit and idempotent
REVOKE ALL ON oauth_tokens, password_reset_tokens, outbox FROM westside_ops_reader;

Verification

# Save the SQL above as /tmp/westside-ops-role.sql
export READER_PW=$(cat ~/secrets/westside-ops/reader-db-password)

# Apply
kubectl exec -n basketball-api $(kubectl get pod -n basketball-api -l app=postgres -o name) -- \
  bash -c "PGPASSWORD=\$POSTGRES_PASSWORD psql -U basketball -d basketball" \
  < /tmp/westside-ops-role.sql

# Verify allowlist (must all succeed, return row counts)
for table in tenants parents players player_teams teams coaches registrations orders products email_log practice_schedules events interest_leads sponsors; do
  kubectl exec -n basketball-api $(kubectl get pod -n basketball-api -l app=postgres -o name) -- \
    psql "postgres://westside_ops_reader:${READER_PW}@localhost:5432/basketball" \
    -c "SELECT COUNT(*) FROM $table;" \
    || echo "FAIL: $table"
done

# Verify blocklist (must all return 'permission denied')
for table in oauth_tokens password_reset_tokens outbox; do
  kubectl exec -n basketball-api $(kubectl get pod -n basketball-api -l app=postgres -o name) -- \
    psql "postgres://westside_ops_reader:${READER_PW}@localhost:5432/basketball" \
    -c "SELECT * FROM $table LIMIT 1;" 2>&1 | grep "permission denied" \
    || echo "SECURITY FAIL: $table was readable!"
done

Acceptance Criteria

• [ ] ~/secrets/westside-ops/reader-db-password exists with 32+ bytes of entropy, not committed anywhere
• [ ] \du westside_ops_reader in the basketball-api postgres pod shows the role exists with LOGIN attribute
• [ ] All 14 allowed tables return a row count when queried as westside_ops_reader
• [ ] All 3 forbidden tables return permission denied when queried as westside_ops_reader
• [ ] SQL file archived at ~/westside-ops/migrations/001-create-reader-role.sql (committed later with the repo bootstrap, for reference)
• [ ] Migration is idempotent — running the SQL twice does not error

Files touched

• ~/secrets/westside-ops/reader-db-password (new, local-only)
• basketball-api Postgres state (CREATE ROLE + GRANTs)
• Eventually: ~/westside-ops/migrations/001-create-reader-role.sql (committed with repo bootstrap ticket)

Rollback

-- Safe to run at any time
DROP ROLE IF EXISTS westside_ops_reader;

Requires disconnecting any active connections first. No data is affected by dropping the role.

Out of scope

• Row-Level Security (RLS) policies — deferred to a later story if Marcus's personal-data view ever needs "Marcus sees only his row" semantics. For v1 operator use, table-level GRANTs are sufficient.
• Read-write (INSERT/UPDATE/DELETE) grants — v1 is read-only per the user story. Write access is a future expansion ticket.
• Any changes to basketball-api's application code, alembic migrations, or Python files.

Dependencies

Streamlit spike ticket must pass veto gate first. Otherwise nothing — this ticket is independent of repo/CI/overlay work and can execute in parallel with them.

Ticket: Streamlit st.data_editor UX veto spike

Story: story-westside-ops-spreadsheet-access
Architecture: arch-deployment-westside-ops, arch-dataflow-westside-ops
Labels: story:spreadsheet-access,arch:streamlit-app,type:spike,track:research,scope:planned
Estimated effort: 30 minutes
Blocks: every other ticket in this project. Blocked by: nothing.

Purpose

This is the veto gate. Before any infrastructure work starts, Lucas needs to see st.data_editor rendering real basketball-api data and decide whether the grid UX is acceptable. If the grid feels wrong, we pivot to NocoDB (per the archived nocodb-basketball-api-scoping note) — no production resources get created in the meantime.

Scope

• Create a throwaway Python venv locally (~/tmp/westside-ops-spike or similar)
• pip install streamlit pandas psycopg2-binary
• Port-forward the basketball-api Postgres pod: kubectl port-forward -n basketball-api postgres-9b5b87b5-5nccx 5432:5432 (in a separate shell — use the running pod name from kubectl get pods -n basketball-api -l app=postgres, not this exact name)
• Write a ~50-line spike.py with one Streamlit page: "Players" — full join of players, parents, teams, filtered to tenant_id=1, rendered with st.data_editor(df, disabled=True, use_container_width=True)
• Connect as the existing basketball role (read the password from ~/secrets/basketball-api/postgres-password). The westside_ops_reader role does NOT exist yet — that's a later ticket. For this spike, we just need to see the grid.
• streamlit run spike.py, open in browser, verify: sort by clicking column headers, per-column filter UI, search box, row selection, Ctrl+C copy of a cell range
• Take 2-3 screenshots: full grid, filtered grid (e.g., division='boys'), and a selected-and-copied cell range

Acceptance Criteria

• [ ] streamlit run spike.py opens a browser tab showing the Players grid with 66 rows
• [ ] Sort works on every column (click header to cycle asc/desc)
• [ ] Per-column filter works (click the filter icon, filter by value)
• [ ] Search box filters across the whole grid
• [ ] Cell selection + Ctrl+C copies tabular text to clipboard (verify by pasting into a text editor)
• [ ] Screenshots captured and shared with Lucas
• [ ] Lucas gives explicit thumbs up OR thumbs down on the UX

Verification

Lucas looks at the screenshots (or runs it himself via the spike.py file) and says one of:

1. "Yes, this works" — all other tickets in board-westside-ops become reviewable. Ava can start the review-ticket loop to promote them to todo.
2. "No, UX is wrong" — this ticket is marked done-with-pivot, and we unarchive the nocodb-basketball-api-scoping note, start a parallel NocoDB scoping effort on a separate spike ticket.

Files touched

• Local only — no repos touched
• ~/tmp/westside-ops-spike/spike.py (throwaway)
• ~/tmp/westside-ops-spike/.venv/ (throwaway)

Rollback

Delete the throwaway directory. No cluster changes, no repo changes, no state to unwind.

Out of scope

• Keycloak OIDC integration — not needed for veto gate, added in the main ticket
• Multiple pages — one page is enough to judge the grid component
• Styling or branding — defaults are fine, we're evaluating the grid, not the chrome
• Write access — disabled=True for the spike
• Any pod, service, or ingress work

Dependencies

None. This is the first thing that happens.

Data Flow: westside-ops

Diagram

Flow 1: First login

sequenceDiagram
    actor Marcus
    participant Phone as Marcus phone(Tailscale client)
    participant TS as Tailscale tailnet
    participant Ingress as westside-ops Ingress
    participant Pod as Streamlit pod
    participant KC as Keycloak(westside realm)
    participant PG as basketball-apiPostgres

    Marcus->>Phone: Open https://westside-ops.tail5b443a.ts.net
    Phone->>TS: HTTPS via tailnet
    TS->>Ingress: Route to westside-ops namespace
    Ingress->>Pod: Proxy :8501
    Pod-->>Phone: 302 → Keycloak /auth
    Phone->>KC: OIDC authorization request(PKCE, client=westside-ops)
    KC-->>Phone: Login page
    Marcus->>KC: Enter westside credentials
    KC->>KC: Validate user + checkwestside-ops-user role
    KC-->>Phone: 302 → westside-ops/callback + code
    Phone->>Pod: GET /callback?code=...
    Pod->>KC: POST /token (code → ID token)
    KC-->>Pod: ID token (signed JWT)
    Pod->>Pod: Validate signature, extract sub + roles
    Pod->>Pod: Store session state in memory(no metadata DB)
    Pod-->>Phone: Render landing page(sidebar with 9 pages)
    Marcus->>Phone: Click "Players"
    Phone->>Pod: GET /?page=Players
    Pod->>PG: psycopg2 connect as westside_ops_reader(cached via @st.cache_resource)
    Pod->>PG: SELECT p.*, pr.email, pr.phone, t.nameFROM players pLEFT JOIN parents pr ON p.parent_id=pr.idLEFT JOIN player_teams pt ON pt.player_id=p.idLEFT JOIN teams t ON t.id=pt.team_idWHERE p.tenant_id=1ORDER BY p.name
    PG-->>Pod: 66 rows
    Pod->>Pod: pd.DataFrame(rows)
    Pod->>Pod: st.data_editor(df, disabled=True)
    Pod-->>Phone: HTML + JS for interactive grid
    Phone-->>Marcus: Rendered grid with sort/filter/copy

Flow 2: Operator cohort task (example — copy all Kings parent emails)

sequenceDiagram
    actor Marcus
    participant Phone
    participant Pod as Streamlit pod
    participant PG as basketball-api Postgres

    Marcus->>Phone: Navigate to Parents page
    Phone->>Pod: GET /?page=Parents
    Pod->>PG: SELECT pr.name, pr.email, pr.phone,p.name AS player, p.divisionFROM parents prJOIN players p ON p.parent_id=pr.idWHERE p.tenant_id=1
    PG-->>Pod: All parents joined with player data
    Pod-->>Phone: Full grid rendered

    Marcus->>Phone: Click "division" column filter
    Phone->>Phone: (client-side: filter rows where division='boys')
    Phone-->>Marcus: ~40 rows (Kings parents)

    Marcus->>Phone: Click "email" column header(select column)
    Marcus->>Phone: Ctrl+C / long-press copy
    Phone-->>Marcus: Emails in clipboard

    Marcus->>Phone: Switch to Gmail app
    Marcus->>Phone: Paste into BCC field
    Note over Marcus,Phone: Zero developer involvement.Zero AI involvement.Zero API writes.Paradigm preserved.

Components

Key Decisions

• OIDC flow is standard PKCE, not a custom auth layer. Streamlit doesn't ship OIDC out of the box, but the community streamlit-keycloak package (and similar alternatives) wire it up in ~30 lines of config. Using a standard PKCE flow means Marcus's Keycloak account (the same one he uses for westside-app) is the single identity — no second password, no invite flow beyond adding the westside-ops-user role.
• Postgres connection is cached via @st.cache_resource. One connection pool per Streamlit pod, reused across requests and sessions. This avoids opening a new Postgres connection on every page click. psycopg2's connection object is thread-safe for the usage pattern (Streamlit serializes requests per session).
• Queries are raw SQL in the Python file, not ORM or dataframe abstractions. Each page has its own SELECT statement with the joins it needs. Raw SQL is transparent (you can read the .py file and know exactly what Marcus sees), debuggable (any query error surfaces immediately with a line number), and fast (no ORM overhead for read-only joins). It also means the queries are pure documentation of the tool's data model.
• All filtering is client-side in the grid, not round-trip to the database. The query returns the full dataset for the page (e.g., all 66 players); Marcus's filter clicks re-render the grid client-side from the already-loaded pandas DataFrame. This means filter latency is zero (no DB hop), and Marcus can rapidly iterate filters without any server work. For datasets that grow past ~10k rows, we'd reconsider — but westside's data sizes are far below that.
• Session state lives in memory, not in a metadata database. When Marcus applies a filter, Streamlit's session state holds it until his session ends or the pod restarts. There is no persistent "saved view" mechanism in v1. If Marcus reloads the page, he re-applies the filter. This is a deliberate simplification — "filter fast every time" beats "store filter configurations forever" for operator workflows. If this proves wrong in practice, a follow-up ticket adds URL query params as lightweight bookmark support.
• Copy-to-clipboard is the integration mechanism, not API calls. v1 does not POST to basketball-api's blast endpoint from a button. Instead, Marcus copies the email column and pastes into Gmail / GroupMe / whatever tool is appropriate for the task. This keeps the v1 surface area tiny (zero write paths, zero cross-service auth, zero API contracts) and matches the "AI never blocks" paradigm — Marcus is fully in control of what gets sent to whom. Integrated action buttons are a later story, added only if Marcus asks for them after using the copy-paste flow.
• No background jobs, no async tasks, no cron. Streamlit is stateless request/response. Every operation Marcus takes is synchronous: click → query → render. This is both simpler and safer — there is no "running job" state to reason about, no way for a background task to silently corrupt data, no cron schedule to maintain.

Related

• arch-deployment-westside-ops — the infrastructure these flows run on
• arch-domain-westside-ops — which tables each query touches and why
• story-westside-ops-spreadsheet-access — the user story driving this flow
• session_2026_04_03_email_overhaul — basketball-api's existing blast endpoint that a future "cohort action button" story would integrate with

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

erDiagram
    TENANTS ||--o{ PARENTS : "scopes"
    TENANTS ||--o{ PLAYERS : "scopes"
    TENANTS ||--o{ TEAMS : "scopes"
    TENANTS ||--o{ COACHES : "scopes"
    TENANTS ||--o{ EVENTS : "scopes"

    PARENTS ||--o{ PLAYERS : "has"
    PLAYERS }o--o{ TEAMS : "player_teams junction"
    COACHES ||--o{ TEAMS : "coaches"

    PLAYERS ||--o{ REGISTRATIONS : "pays for"
    PLAYERS ||--o{ ORDERS : "purchases"
    PRODUCTS ||--o{ ORDERS : "sold as"

    TEAMS ||--o{ PRACTICE_SCHEDULES : "has"
    TEAMS ||--o{ EVENTS : "participates in"

    PARENTS ||--o{ EMAIL_LOG : "receives"
    PLAYERS ||--o{ EMAIL_LOG : "tagged in"

    SPONSORS {
        int id
        string name
        string status
    }

    INTEREST_LEADS {
        int id
        string player_name
        string parent_email
    }

    TENANTS {
        int id
        string slug
        string name
        string contact_email
    }

    PARENTS {
        int id PK
        int tenant_id FK
        string name
        string email
        string phone
        bool waiver_signed
    }

    PLAYERS {
        int id PK
        int parent_id FK
        int tenant_id FK
        string name
        enum division
        enum jersey_order_status
        enum contract_status
        enum subscription_status
    }

    TEAMS {
        int id PK
        int tenant_id FK
        string name
        enum division
        enum age_group
        int coach_id FK
    }

    COACHES {
        int id PK
        int tenant_id FK
        string name
        string email
        enum role
        enum onboarding_status
    }

    EMAIL_LOG {
        int id PK
        int tenant_id FK
        int parent_id FK
        int player_id FK
        enum email_type
        string recipient_email
        datetime sent_at
    }

Components

Exposed tables (14 total) — grouped by Streamlit page

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)

The westside_ops_reader role

-- One-shot migration, applied via kubectl exec against basketball-api postgres
CREATE ROLE westside_ops_reader LOGIN PASSWORD :'password';
GRANT CONNECT ON DATABASE basketball TO westside_ops_reader;
GRANT USAGE ON SCHEMA public TO westside_ops_reader;

GRANT SELECT ON
  tenants, parents, players, player_teams, teams, coaches,
  registrations, orders, products, email_log,
  practice_schedules, events, interest_leads, sponsors
TO westside_ops_reader;

-- Explicitly NOT GRANTED (and the default is no access):
--   oauth_tokens, password_reset_tokens, outbox, alembic_version

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.

Related

• 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)

Deployment: westside-ops

Diagram

graph TB
    Marcus[Marcus phoneiOS Safari + Tailscale client]

    subgraph tailnet[Tailscale tailnetACL gated]
        TS[Tailscale operatortailscale.com/expose]
    end

    Marcus -->|HTTPSwestside-ops.tail5b443a.ts.net| TS

    subgraph k3s[k3s cluster - archbox]
        subgraph ws_ns[namespace: westside-ops]
            Ingress[Ingresswestside-ops-ingress]
            Svc[Servicewestside-ops :8501]
            Pod[Streamlit podwestside-ops/app from Harborst.data_editor grids]
            SOPSSecret[secrets.enc.yamlSOPS age-encryptedOIDC client + DB URL]
        end

        subgraph bb_ns[namespace: basketball-api]
            BBPG[(postgres podbasketball DBTier 2 per-service)]
            BBApp[basketball-api appunchanged]
        end

        subgraph kc_ns[namespace: keycloak]
            KC[Keycloakrealm: westsidenew client: westside-ops]
        end

        subgraph argo_ns[namespace: argocd]
            ArgoApp[ArgoCD Applicationwestside-opssource: westside-ops repo /k8s]
        end
    end

    subgraph harbor_ns[Harbor registry]
        HarborImg[harbor.tail5b443a.ts.net/westside-ops/app:SHA]
    end

    TS --> Ingress
    Ingress --> Svc
    Svc --> Pod
    Pod -.OIDC redirect.-> KC
    Marcus -.Keycloak login.-> KC
    KC -.ID token.-> Pod
    Pod -->|psycopg2 aswestside_ops_reader| BBPG
    Pod -.image pull.-> HarborImg
    ArgoApp -.sync.-> Pod

    style Marcus fill:#f9f,stroke:#333
    style BBPG fill:#ffd,stroke:#333
    style KC fill:#dfd,stroke:#333
    style Pod fill:#ddf,stroke:#333

Components

Key Decisions

• Workload, not platform capability. westside-ops is a westside-specific operator tool — it serves one tenant's data via one instance. That makes it a workload, not a platform capability, and workloads follow the kustomize-overlay pattern (like basketball-api, westsidekingsandqueens, mcd-tracker) rather than the Helm-release-in-tofu pattern (like Harbor, Keycloak, CNPG operator). Zero pal-e-platform changes.
• Sibling repo, not folded into basketball-api. A separate Forgejo repo forgejo_admin/westside-ops preserves basketball-api's hands-off status. The only basketball-api touch is one SQL migration (the westside_ops_reader role). Upgrades, new Streamlit pages, and Marcus-specific customizations all happen in westside-ops without touching basketball-api's code, tests, or deploy pipeline.
• Tailscale expose, not funnel. This is a window into production data. Defense in depth demands a network-layer gate before any application-layer auth. With tailscale.com/expose: "true", the login page is only reachable from tailnet members. Marcus installs Tailscale on his phone (one-time, 3 minutes), Lucas adds him to the tailnet, and Keycloak becomes the second gate instead of the only one. Public funnel was explicitly rejected — the cost of reversing later is too high if data leaks, and Marcus installing Tailscale is trivially cheap.
• Defense-in-depth at the Postgres role level, not the application level. The westside_ops_reader role has explicit GRANT SELECT on 14 tables and nothing else. oauth_tokens (which contains Gmail access and refresh tokens in JSONB), password_reset_tokens, and outbox are unreachable at the DB layer. This means even a Streamlit bug that tried to execute arbitrary SQL would get permission denied on the forbidden tables — security does not depend on the UI being correct.
• No metadata database — Streamlit is stateless by design. Unlike NocoDB (which needs its own Postgres for views, user mappings, API tokens), Streamlit's state lives in the streamlit_admin.py file itself (queries and page definitions, versioned in git) and per-request session state (in-memory). If the pod restarts, Marcus re-logs in via Keycloak; nothing else is lost. This eliminates the "CNPG Tier-1 cluster for metadata" requirement and the associated backup ceremony entirely.
• Read-only for v1. Streamlit supports editable grids via st.data_editor(df, disabled=False), and the westside_ops_reader role grants only SELECT. Flipping to read-write later is one SQL grant (GRANT INSERT, UPDATE ON players TO westside_ops_reader) plus one Python arg flip — but v1 ships read-only because "Marcus can't accidentally corrupt production" is a more valuable property than "Marcus can edit from his phone."
• Harbor-mirrored Streamlit base image. The container image is built from python:3.12-slim + pip install streamlit + the app source, then pushed to harbor.tail5b443a.ts.net/westside-ops/app:{SHA}. Every workload image in the cluster comes from Harbor — no direct Docker Hub pulls — so westside-ops inherits vulnerability scanning and supply-chain provenance automatically.
• Soft launch via direct DM, not integrated links. v1 ships at a standalone URL not linked from westside-app's existing admin. Lucas DMs Marcus the URL privately. If Marcus likes it, phase 2 can consider adding a "Data Grid" card on westside-app's admin dashboard (option B from the rollout plan) or migrating parts of the existing admin into Streamlit pages (option C). Keeping the v1 rollout decoupled means zero risk to Marcus's current workflow.

What changes in each repo

Related

• story-westside-ops-spreadsheet-access — the user story this architecture serves
• arch-domain-westside-ops — which tables are exposed and how they group into pages
• arch-dataflow-westside-ops — runtime flow: Marcus → Tailscale → Keycloak → Streamlit → Postgres
• nocodb-basketball-api-scoping (archived) — the schema audit + GRANT allowlist + NocoDB evaluation that informed this architecture
• feedback_basketball_hands_off, feedback_enterprise_no_workarounds, feedback_never_stomp_archbox — relevant memories
• board-westside-ops — tickets serving this architecture

story: Operator Spreadsheet Access to Westside Data

Role

Westside Operator — Marcus initially (Lucas's brother, coach candidate, active player), with coaches and other Westside staff as the expansion audience in later phases.

Key

spreadsheet-access

Want

As a Westside operator, I want to view all Westside basketball data in a spreadsheet-style interface with sort, filter, search, and copy-to-clipboard capabilities — without any AI, developer, or admin in the loop.

So That

So that I can independently accomplish the manual operational tasks I own — chasing unsigned contracts, verifying jersey orders, collecting missing waivers, contacting cohorts of parents, pulling lists for GroupMe messages, tracking sponsor outreach, and responding to whatever ad-hoc operational question comes up this week — without waiting for anyone to build a view for me or query the database on my behalf.

Paradigm (why this story exists)

AI should never be a blocker. The current state is that Marcus cannot see or use Westside data directly — his options are "ask Lucas" or "ask an AI agent to query for him." Both of those make a human or AI a dependency in the operator's critical path. That's the pattern this story eliminates. Direct, immediate, unfiltered access to production data (read-only, through a defense-in-depth restricted role) is the correct state. AI becomes an accelerator for tasks like "draft this email" or "summarize this cohort" — it does not become the sole path to the data.

Acceptance Criteria

• [ ] Marcus can navigate to https://westside-ops.tail5b443a.ts.net from his phone (Tailscale-private) and log in via Keycloak using his existing westside realm account
• [ ] After login, Marcus sees a sidebar of pages covering every major data surface: Players, Parents, Teams & Rosters, Contracts, Jerseys & Orders, Email Log, Schedule, Coaches, Sponsors
• [ ] Every page renders the full relevant dataset in a Streamlit st.data_editor grid with: column header sort, per-column filter, full-text search, row selection, cell selection, Ctrl+C copy-to-clipboard
• [ ] Marcus can filter to "all Kings" by clicking the division column filter — no developer involvement, no new page required
• [ ] Marcus can copy the email column of a filtered view and paste it into Gmail or GroupMe without reformatting
• [ ] The underlying data is live — changes made via westside-app's admin (or by basketball-api workflows) appear on Marcus's next page refresh
• [ ] westside-ops is read-only in v1 — no accidental edits, no production data corruption risk
• [ ] Security-critical tables (oauth_tokens, password_reset_tokens, outbox) are not reachable at the Postgres role level — not just hidden in the UI, literally permission denied if a bug tried to query them
• [ ] The tool works on Marcus's phone (iOS Safari, 390px viewport) — grids are scrollable and readable on mobile
• [ ] Marcus does not need to learn a query language, a view-builder grammar, or any developer tooling — only the standard spreadsheet UX he already knows

Success Metric

After 1 week of westside-ops being live, Marcus has independently completed at least 5 operational tasks (e.g., "emailed all Kings parents," "pulled a list of unsigned contracts," "copied practice-schedule info for a coach") without asking Lucas, Ava, or any AI agent to query or navigate data on his behalf. Measured by conversation review (if Marcus's Westside-related messages to Lucas drop by >50% while his operational output stays constant or increases, the story is fulfilled).

Related Architecture

• arch-deployment-westside-ops — how westside-ops runs (Streamlit pod + private Tailscale + Keycloak OIDC + restricted Postgres role)
• arch-domain-westside-ops — the subset of basketball-api tables exposed through the westside_ops_reader role and how they're grouped into the 9 Streamlit pages
• arch-dataflow-westside-ops — the runtime flow: Marcus → Tailscale → Keycloak OIDC → Streamlit → read-only Postgres → st.data_editor grid

Related

• board-westside-ops — project board where tickets serving this story live
• nocodb-basketball-api-scoping — archived scoping research (NocoDB was evaluated and deferred in favor of Streamlit for tighter stack fit; the schema audit, GRANT allowlist, and workflow analysis in that note remain authoritative)
• feedback_paldocs_first, feedback_basketball_hands_off, feedback_marcus_plain_language, feedback_discovered_scope_always_tracked — relevant memories
• project_marcus_second_tryout, project_marcus_doordash_onboarding, user_brother_marcus — Marcus context memories
• session_2026_04_03_email_overhaul — basketball-api's existing email blast endpoint that future cohort-action tickets will integrate with

Explicitly out of scope for v1

• Write access — v1 is read-only. Editing rows comes later, gated on Marcus wanting it and on a backup/undo story.
• Cohort-action buttons (e.g., "email all selected") — v1 uses copy-to-clipboard as the bridge to Gmail/GroupMe. Integrated action buttons come after Marcus has validated the read-only shape.
• Parent-facing or coach-facing filtered views — v1 is Marcus only. Row-level access for other roles comes in a later story.
• Marcus's personal DoorDash/LLC data — distinct dataset, distinct schema, deferred to a future story that reuses the same tool.
• Any changes to westside-app — the existing SvelteKit admin stays untouched. westside-ops is purely additive.