Skip to content

Spec: connect every Supabase table to the KG in both directions, then KG reads and writes over MCP #522

Open

Description

@nadernik

Status: draft. @nadernik to confirm the classification and the open questions.

Decision (nadernik, 2026-11-19): first, everything in Supabase connects to the KG in both directions, so operations like onboarding aren't disrupted. Then MCP query, read and write work on that graph. The second MCP server (#520) is not merged until this is specced and its first phases are met.

Why

  • Onboarding runs on data the graph doesn't hold.
    • These investigator columns aren't in investigators_public, so no export carries them: email, secondary_emails, institution, onboarding_checklist, onboarding_completed_at, requested_working_groups, reporter_profile_id, and the two reminder fields. That is 9 of 23 columns.
    • These tables aren't exported at all: user_roles (98 rows), profiles (93), access_requests (92), slack_channel_members (562) and group_audit_dismissals.
    • A tool that reads the graph can't see any of this state.
  • Nothing reads the graph through MCP today. bbqs-mcp's kg_query is a PostgREST read run under RLS. Every MCP write is a database RPC that names its actor, and that part is right.
  • feat(kg): read-only MCP server over the exported graph #520 changes onboarding write paths. It adds create_grant and get-or-create helpers (_shared/kg-write.ts). It rewrites bbqs-mcp, add-project-by-grant, nih-grants, seed-consortium, verify-affiliations and import-grant-publications, and adds unique indexes. Nothing yet shows that those paths leave the database in the state they did before.

Where it stands (production, 2026-11-19)

There are 78 tables and 22 views in public.

Group Tables Rows In the graph today
Exported entities and links 19 1,730 Anon projection only. investigators is exported through investigators_public, so 14 of its 23 columns.
Other domain tables 27 5,966 No
Membership and access 9 1,151 No
Audit and provenance 7 103,532 No
Telemetry 4 45,969 No
Lovable billing 5 21,187 No
Pipeline internals 7 7,501 No
  • DB → KG: kg/roundtrip.py measures 18 tables on the anon export: 71.7% of cells carried (11-04).
  • KG → DB: nothing measures this direction.

Phases

Each phase has a test that shows it's done, and each gates the next.

  1. Inventory.

    • A manifest (kg/table_map.yaml) lists every table and view. Each is in the graph (as a class, a link, or attributes of another node) or out of it, with a reason.
    • For each in-graph table, every column names its slot and its visibility: public, members or curators.
    • A guard fails when a migration adds a table or column that the manifest doesn't list.
    • Done when the guard is in npm run test:guards and passes.
  2. DB → KG, lossless.

    • A full-access export carries every non-null cell of every in-graph table. kg/roundtrip.py measures it per table and column.
    • The anon export stays the committed public projection. The full export is never committed or published.
    • Done when 100% of cells are carried for the in-graph tables, and a check shows that the anon projection holds no members-only or curators-only column. The check compares column names, not markers.
  3. KG → DB, rebuild.

    • A script loads the full export into an empty copy of the schema (a Supabase branch or local Postgres).
    • A diff compares that copy to production, table by table.
    • Done when every in-graph table rebuilds with identical rows. The out-of-graph tables are restored from backups, not from the graph.
  4. Operations unchanged. A scripted run of the operations, against a branch database with fixture people:

    • the onboarding sequence: create_grant → onboard_member → sync_member_groups → set_onboarding_step → send_welcome_email (dry run) → refresh_grant_from_reporter
    • approving an access request, and offboard_member

    The run records the database state after each step. A change to a write path (edge function, RPC, trigger, unique index) must leave that record unchanged, or name the difference it intends.
    Done when the run is in CI, or in a script with a committed baseline, and the feat(kg): read-only MCP server over the exported graph #520 write-path changes pass it.

  5. MCP reads and writes on the graph, in bbqs-mcp. One endpoint, three tiers.

    • Reads: graph query and node lookup. Anon sees the public projection. Members and curators see what their RLS lets them read.
    • Writes stay database writes through the existing tools, which are RPCs that call set_actor. Nothing writes graph files directly. A write returns the node's IRI, and a graph read shows it within the freshness bound (open question 3).
    • Query limits are enforced outside the serving process: a hard timeout that can't stall other callers, and a cap on result size.
    • Done when the competency questions answer through MCP on all three tiers, and a person added with onboard_member appears in a graph read.
  6. Then the second server. feat(kg): read-only MCP server over the exported graph #520's kg/mcp_server.py is weighed against phase 5. Either its tools fold into bbqs-mcp, or this spec records why a separate service is needed and how it handles sign-in.

Principles this keeps

  • III, live state: the database, Google Groups, Forms and the grant roster stay the systems of record. The graph is derived from them and never decides entitlement by itself.
  • X, provenance: every write still names its actor.

Proposed classification (to confirm)

In the graph (49):

  • the 19 exported tables
  • other domain tables (23):
    • jobs, funding_opportunity_orgs, project_software_tools, resource_links
    • publication_fulltext, publication_fulltext_body, publication_figure (curators only)
    • species_candidates, cohort_summaries, slack_channels, entity_comments
    • device_class_crosswalk, the 4 device_category_* tables (empty)
    • grant_methods_evidence, grant_methods_traversal_paths
    • reporter_pi_observations, source_classes, state_privacy_rules
    • meetings, meeting_turns (empty; wait for the consent decision)
  • membership and access (7, members or curators only): user_roles, profiles, access_requests, allowed_domains, group_audit_dismissals, slack_channel_members, slack_channel_pending

Your call (11):

  • curation queues and site feedback: news_candidates, proposed_relations, feature_suggestions, feature_votes
  • audit and provenance: field_provenance, data_audit_log, curation_audit_log, auth_audit_log, edit_history, security_audit_results, system_alerts

Out (18), with the reason:

  • telemetry, about use of the site rather than facts about the consortium: analytics_clicks, analytics_pageviews, mcp_usage_log, search_queries
  • Lovable billing, which goes with Lovable: budget_config, budget_snapshots, lovable_credit_events, lovable_invoices, lovable_user_usage
  • pipeline internals: the 6 harvester_* tables, and knowledge_embeddings, which is derived from text and gets rebuilt, not restored
  • UI settings: user_dashboard_layouts, working_group_dashboard_defaults

Views aren't separate facts. Each is listed with the tables it reads.

Open questions

  1. Provenance: carry field_provenance as PROV-O (92,463 rows), or only the current claim per cell (field_provenance_current)? Should the audit logs stay database-only?
  2. Group and Slack membership have external systems of record. Should the graph carry the observed snapshot (slack_channel_members, slack_channel_pending), or leave them out so nothing can decide from a copy?
  3. Freshness: how far behind the database may the graph be? Re-export on each write, or on a schedule (daily today)?
  4. The full-access export: where does it run? bbqs-mcp deliberately holds no service-role key.
  5. Where this spec lives: bbqs-agent/specs/ is the spec home, but bbqs-agent is paused. Proposal: keep it here until bbqs-agent resumes, then move it there as its next spec.

Not in this spec

  • New surfaces built on the graph, such as the Meetings and Devices tabs, until phase 4 passes.
  • Changing what the public site shows.

Refs #451, #386, #520, #513

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions