app/worker/migrations/0004_repo_roles_cache.sql

zzmq.dev@gmail.com/zz / app / worker / migrations / 0004_repo_roles_cache.sql @ 867419d4 Download

Ladder Summary

file This migration creates a new `repo_roles` table that caches each repository’s current `roles.json` grant (one row per `repo_id` + `principal`) and adds an index on the `principal` column to support fast “repos you have a role on” queries. The table stores the role, who granted it, and when, and is kept in sync by the `doRbacGrant` function elsewhere in the codebase. No functions or classes are exported from this SQL file.

-- A D1-side cache of each repo's roles.json grants (source of truth

-- stays the zz-meta branch -- see repoMeta.ts/architecture/index.md's

-- warning against D1 becoming load-bearing). Needed because "repos

-- you have a role on" is a cross-repo query (the profile page), and

-- there's no affordable way to answer that by walking every repo's

-- own orphan branch on every page load -- same reasoning the `repos`

-- table itself already documents for why it exists as an index.

-- Kept in sync by doRbacGrant (webApp.ts) on every grant/role change;

-- one row per (repo, principal), since a principal has exactly one

-- current grant per repo at any time (roles.json itself only ever

-- keeps the latest).

CREATE TABLE repo_roles (

repo_id TEXT NOT NULL REFERENCES repos(id),

principal TEXT NOT NULL,

role TEXT NOT NULL CHECK (role IN ('owner', 'maintainer', 'contributor', 'reader')),

granted_by TEXT NOT NULL,

at TEXT NOT NULL,

PRIMARY KEY (repo_id, principal)

);

CREATE INDEX idx_repo_roles_principal ON repo_roles(principal);