pal-e-docs

NocoDB

nocodb forgejo

Board

Notes

Doc 1
  • NocoDB Scoping: basketball-api Schema Audit nocodb-basketball-api-scoping

    NocoDB Scoping: basketball-api Schema Audit

    Purpose: Decide which tables in basketball-api's Postgres can be exposed through a self-hosted NocoDB instance so Marcus (and eventually other westside stakeholders) can view and edit their own data. This note is the scoping input for the backlog ticket — not a plan. The veto gate is Lucas running docker run nocodb/nocodb locally and deciding the UX is acceptable.

    Context

    • Source repo: ~/basketball-api/src/basketball_api/models.py (single file, SQLAlchemy, 18 tables)
    • Target: NocoDB self-hosted via pal-e-services onboarding, attached to the existing basketball-api Postgres as a read/write "smart view" — NocoDB does not own the schema
    • Auth: Keycloak OIDC, one new realm client, roles for row/table visibility
    • basketball-api is hands-off per memory — we read the schema, we do not modify it. Any table-structure changes become their own tickets on the basketball board, not this one.

    Tables — Classification

    ✅ Expose (read/write)

    Table Purpose Why Marcus wants it
    players Player profile, jersey order, contract, subscription See his own profile, update height/position/photo, check jersey status
    parents Parent contact info, waiver His mom's row — verify contact info, address
    registrations Stripe checkout / tryout payments See what he's been charged for and when
    orders Jersey/contract/tournament purchases Order history, payment status
    coaches Coach info, contractor agreement, Stripe Connect If Marcus is onboarded as a coach, he needs visibility into his own onboarding status

    ✅ Expose (read-only reference)

    Table Purpose
    teams Team roster — show Marcus which team he's on
    player_teams Junction table — his team assignments
    practice_schedules Recurring practice times for his team
    events Tournaments, games, tryouts
    email_log What emails have been sent to him (transparency)
    products Price list reference
    tenants Just "westside" for now, but multi-tenant aware
    interest_leads Public form submissions (admin-only view)

    🚫 NEVER expose — security critical

    Table Why blocked
    oauth_tokens Contains Gmail access and refresh tokens in JSONB. Exposing this in NocoDB = credential disclosure. Must be filtered at the database-user level, not just hidden in NocoDB UI.
    password_reset_tokens Auth secrets. Short-lived but still privileged.
    outbox Internal event queue. Not data Marcus needs; exposing it invites confusion and accidental edits to in-flight events.

    Enforcement mechanism: Create a dedicated Postgres role nocodb_reader (or nocodb_rw) with explicit GRANT SELECT/GRANT INSERT,UPDATE only on the allowed tables. Do not grant on the full schema. NocoDB connects as this role. This is defense-in-depth — even if a NocoDB misconfiguration or UI bug exposed a "show all tables" path, the role simply can't see the excluded tables.

    Schema Smells That Affect NocoDB UX

    1. players is 40+ columns wide. It mixes identity (name, DOB, school), jersey fields (option, size, number, order status), contract fields (status, token, signature URL, version), and Stripe fields (customer id, subscription id, status). NocoDB will render this as a wall of columns. Mitigation: use NocoDB "views" to group columns by concern — "Profile view," "Jersey view," "Contract view." This is a NocoDB configuration task, not a schema change.
    2. JSONB columns render awkwardly. products.custom_fields, orders.custom_data, oauth_tokens.token_data. NocoDB treats JSONB as a long text field. Acceptable for read, ugly for edit. oauth_tokens is already blocked; the other two are rarely touched.
    3. Enums render as dropdowns. Good news — SQLAlchemy's Enum columns (PaymentStatus, JerseyOption, ContractStatus, etc.) will give NocoDB constrained-value editors automatically. Nothing to do.
    4. No row-level scoping in the current schema. There's no owner_user_id or equivalent on players/parents/orders. "Marcus sees only Marcus's row" is not free — it requires either (a) Postgres Row-Level Security policies keyed on a session variable set from Keycloak, or (b) NocoDB per-view row filters applied at the app layer. (a) is the correct answer for defense-in-depth; (b) is faster to ship.
    5. Everything is tenant_id-scoped. Multi-tenancy is already wired — only one tenant (westside) exists today, but the schema is ready for more. NocoDB views should filter on tenant_id = <westside> to future-proof.

    Open Questions (for Lucas after the Docker veto gate)

    1. Read-only or read-write for v1? Starting read-only is lower risk — Marcus sees his data, cannot accidentally corrupt it. Read-write comes after trust is established. Recommendation: read-only v1.
    2. Row-level permissions: Postgres RLS or NocoDB view filters? RLS is the right answer architecturally but adds a migration to basketball-api (hands-off = separate ticket). View filters are faster but mean a NocoDB misconfiguration could leak data. Recommendation: view filters for v1, RLS as a follow-up ticket.
    3. Scope of the v1 launch — Marcus only, or all parents? Marcus-only ships in a day. All-parents means a real auth story (every parent with a Keycloak account, mapped to their parent_id). Recommendation: Marcus only for v1, broaden in v2.
    4. Keycloak realm — new, or reuse existing westside? Reuse is cheaper and matches the "one realm, many services" pattern in SERVICE_ONBOARDING.md. Recommendation: reuse westside realm, add a nocodb client.

    Discovered Scope (candidate tickets for board-nocodb backlog)

    Per feedback_discovered_scope_always_tracked, these all become backlog items — not decisions to make right now:

    1. Spike: Stand up NocoDB locally via Docker, attach to a throwaway Postgres, validate UX with the full basketball-api schema. (Veto gate — Lucas owns this.)
    2. Feature: Add NocoDB to var.services in pal-e-services (Harbor project, namespace, ArgoCD app, Tailscale funnel, Keycloak client in westside realm).
    3. Feature: Create nocodb_reader Postgres role in basketball-api with explicit GRANTs on allow-listed tables only. Block oauth_tokens, password_reset_tokens, outbox at the role level.
    4. Feature: Deploy NocoDB overlay to pal-e-deployments with env pointing at basketball-api-db.basketball-api.svc.cluster.local as nocodb_reader.
    5. Feature: Configure NocoDB "Marcus view" — read-only, filtered to Marcus's player_id, grouped by concern (profile / jersey / contract / payments).
    6. Feature (follow-up): Postgres RLS policies on players, parents, orders, registrations keyed on a session variable set from the Keycloak sub claim. Replaces NocoDB view filters as the security boundary.
    7. Feature (v2): Parent-facing NocoDB access — every parent in the parents table gets a Keycloak account mapped to their parent_id.
    8. Spike: Evaluate Postgres row-level backup/restore story for NocoDB-originated edits. If Marcus fat-fingers his jersey size, how do we roll it back?

    What Happens Next

    1. Lucas runs NocoDB in Docker locally against a throwaway DB (10 minutes). If the UX is unacceptable, we pivot to Teable or abandon. Nothing else moves until this gate passes.
    2. If approved, this scoping note gets linked from each backlog ticket, and tickets get /review-ticket'd before moving from backlog → todo.
    3. First ticket to dispatch is the nocodb_reader Postgres role — it's the smallest, most isolated piece, and it de-risks everything downstream.

    References

    • basketball-api models: ~/basketball-api/src/basketball_api/models.py
    • pal-e-services onboarding: ~/pal-e-services/SERVICE_ONBOARDING.md
    • NocoDB docs: docs.nocodb.com
    • Feedback memories: feedback_basketball_hands_off, feedback_discovered_scope_always_tracked, feedback_ticket_review_gate
Board 1
  • NocoDB board-nocodb

    No content