Org-scoped teacher read filter — build plan (Wave-2 prerequisite #2)

Status: BUILT (2026-07-01) — the filter (buildTeacherManagedOrganizationRowFilterWhere) + its claim readers (readTeacherGrantOrgIds / readTeacherGrantOfferingIds) + the org-teacher write RLS builder (buildClaimAwareOrganizationTeacherManageNativePolicies) shipped, and grade_category is converted end-to-end (§4). Decisions A(i) + B applied (see §6). The filter is now reusable for media_resource and the teacher-side cat_pool read (§5, not yet built). Companion to wave-2-sync-migration-disposition.md and admin-sync-build-plan.md. Was the last remaining structural gate for Wave-2: an organization-scoped read filter for teachers, the read-path twin of canManageTeacherOffering. It unblocks the org-scoped, teacher-authored/consumed reference data (grade_category, media_resource, teacher-side cat_pool reads). No pgxsinkit change is required — the same customWhere grammar the admin filters already use covers this.

1. Why a new filter (what the existing ones can’t do)

The registry has three families of row filter today:

• owner-scoped — buildPersonOwnerRowFilterWhere(col) (row-filter-claims.ts): col = person_id.
• offering-membership — offeringMembershipFanOut(col) (learning-structure.ts) and groupMembershipManageOrOwnFanOut (groups.ts): col in (select offering_id from offering_membership where person_id = $1 …). Offering-keyed.
• admin org-scoped — buildAdminOrganizationRowFilterWhere(col) + the offering→org / foreign-org variants (row-filter-claims.ts, authorization.ts): platform-admin → all, org-admin → org-ids from JWT grants as literals, else deny. Admin grants only.

The deferred tables (grade_category, media_resource, cat_pool) are organization-keyed (each has an organization_id; verified — no offering_id on the reference row) and are authored/consumed by teachers, not just admins. None of the three families fits:

• owner/offering filters are the wrong key (the row has an org, not an owner or an offering);
• the admin filter only reads organization_admin / platform_admin grants — a plain teacher (teacher/assistant grant) would sync nothing.

We need the read twin of canManageTeacherOffering (apps/api/.../teacher-offering/service.ts:1050), generalized to an org column: a caller may read a row when they administer or teach in its organization. The write-side twin already exists — buildClaimAwareTeacherManagePredicate (claim-aware-policies.ts:393) scans the same JWT grant array for exactly these branches.

2. Grant model (the source of truth)

Authorization is person_role_grant (authorization.ts:25), projected to the JWT as app_metadata.authorization.grants[] = { role, scope: { kind, organizationId?, offeringId? } } (ACTIVE grants only, refreshed on the token cadence). canManageTeacherOffering accepts four branches:

1. platform_admin (scope.kind platform) → everything;
2. organization_admin on the row’s org;
3. teacher/assistant organization grant on the row’s org;
4. teacher/assistant offering grant on an offering that belongs to the row’s org.

The read filter mirrors these. Branches 1–3 are pure JWT reads (org-ids become escaped literals, exactly like the admin filter). Branch 4 carries an offeringId (not an org), so it needs a one-hop offering → organization_id subquery — the same shape as buildAdminOfferingOrgRowFilterWhere, proven against real Electric in tests/integration/electric-subquery-grammar.integration.test.ts.

3. New code

3.1 Claim readers — packages/db/src/schema/row-filter-claims.ts

Beside readOrganizationAdminOrgIds, add the teacher-grant readers (same defensive parse, same teacher/assistant role set as the service’s teacherManageRoleValues):

const TEACHER_MANAGE_ROLES = ["teacher", "assistant"] as const;

/** Org-ids from the caller's ACTIVE org-scoped teacher/assistant grants (JWT). De-duplicated. */
export function readTeacherGrantOrgIds(claims: JwtClaims): string[] {
  const ids = new Set<string>();
  for (const grant of readAuthorizationGrants(claims)) {
    if (
      TEACHER_MANAGE_ROLES.includes(grant.role as (typeof TEACHER_MANAGE_ROLES)[number]) &&
      grant.scope?.kind === "organization" &&
      typeof grant.scope.organizationId === "string" && grant.scope.organizationId.length > 0
    ) {
      ids.add(grant.scope.organizationId);
    }
  }
  return [...ids];
}

/** Offering-ids from the caller's ACTIVE offering-scoped teacher/assistant grants (JWT). */
export function readTeacherGrantOfferingIds(claims: JwtClaims): string[] { /* symmetric on scope.kind === "offering" / scope.offeringId */ }

Rename the misnamed buildOrganizationAdminInClause to a generic buildUuidLiteralInClause(column, ids) (it already emits col::text in ('uuid', …) for any uuid column) and keep the old name as a thin re-export for back-compat — the offering→org disjunct reuses it for offering.id.

3.2 The filter — packages/db/src/schema/authorization.ts

Beside the admin builders (it needs offeringTable, already imported here):

/**
 * Read filter for an ORG-keyed row a TEACHER may see — the read twin of canManageTeacherOffering.
 * platform_admin → null (all). Otherwise the union of:
 *   • rows in an org the caller administers OR teaches at org scope   (direct literal IN)
 *   • rows in the org of an offering the caller teaches at offering scope (offering→org subquery)
 *   • [opt] global rows with a null org                                (includeGlobalNull)
 * No qualifying grant → DENY_ALL. All disjuncts are proven Electric grammar (literal IN,
 * single-level IN-subquery, IS NULL); OR-of-subquery is the groupMembershipManageOrOwnFanOut shape.
 */
export function buildTeacherManagedOrganizationRowFilterWhere(
  organizationColumn: AnyColumn,
  options: { includeGlobalNull?: boolean } = {},
): (claims: JwtClaims) => SQL | null {
  return (claims) => {
    if (isPlatformAdmin(claims)) return null;
    const directOrgIds = [...new Set([...readOrganizationAdminOrgIds(claims), ...readTeacherGrantOrgIds(claims)])];
    const offeringIds = readTeacherGrantOfferingIds(claims);
    const disjuncts: SQL[] = [];
    if (directOrgIds.length) disjuncts.push(buildUuidLiteralInClause(organizationColumn, directOrgIds));
    if (offeringIds.length)
      disjuncts.push(
        sql`${c(organizationColumn)} in (select ${c(offeringTable.organizationId)} from ${offeringTable} where ${buildUuidLiteralInClause(offeringTable.id, offeringIds)})`,
      );
    if (options.includeGlobalNull) disjuncts.push(sql`${c(organizationColumn)} is null`);
    if (!disjuncts.length) return DENY_ALL;
    return disjuncts.reduce((acc, d) => sql`${acc} or ${d}`);
  };
}

3.3 Grammar probe

Add one case to electric-subquery-grammar.integration.test.ts: a col::text in (lit) or col in (select … where id::text in (lit)) shape (literal-IN OR subquery-IN), asserting row-level correctness. Every constituent is already proven; this pins the exact OR-composition.

4. First cutover — grade_category (the proof)

gradeCategoryTable (assessment-feedback.ts:74) is org-keyed (organization_id, nullable — null = a platform-global default), teacher-authored via the teacher-portal ManageWeightingModal (REST …/grade-categories). Steps:

1. defineSyncTable it (readwrite, +LWW), replacing the plain pgTable, mirroring offering_resource. shape.rowFilter: (cols) => buildTeacherManagedOrganizationRowFilterWhere( cols.organizationId, { includeGlobalNull: true }) (see §6 decision B).
2. Write RLS. Reuse buildClaimAwareTeacherManageNativePolicies with the org expression (see §6 decision A for the offering-teacher write story).
3. Applier branch in apps/api/.../sync-mutation-applier.ts (create/update; managed fields — createdBy authClaim + audit — server-stamped; no hard delete, mirror the REST retire).
4. Registry (packages/db/src/sync-registry.ts): add gradeCategory to a teacher-authored group in the authoritative registry and to teacherSyncRegistry. Do not add it to the learner registry (the learner reads computed weighted finals via the REST gradebook — this sidesteps any learner over-share). The single claims-branching customWhere already admits org-admins, so adding it to adminSyncRegistry later is a one-line change, no filter edit. assertReadContractPreserved guards the projection.
5. offline-data: overlay _read_model view name + erasure-registry entry (it gains createdByPersonId → person-linked; add it before regenerating so identity_linking’s erasure guard doesn’t drift — the cat_pool lesson).
6. Migrations: full-regenerate per the runbook (the rm -rf src/migrations/*/ prompt is Anton’s approve-the-gate step; the agent runs the rest).
7. Integration test grade-category-sync-cycle.integration.test.ts (add to test:integration:compose), proving vs real Postgres+Electric: org-admin and org-teacher see their org’s categories; an offering-teacher sees the categories of their offering’s org; a cross-org teacher sees none; a platform admin sees all; global (null-org) rows visible per decision B; grantless write RLS-denied.
8. UI cutover: teacher-portal TeacherSyncProvider exposes gradeCategories (overlay view) + createGradeCategory/updateGradeCategory; ManageWeightingModal reads and writes over sync and the REST write path is retired from the modal. The sync provider is the offline layer — there is no REST fallback (component tests mount a test provider). The line-item→category assignment (assignTeacherLineItemGradeCategory) is a separate offering-scoped row that also converts to sync (offeringMembershipFanOut) — tracked as its own task, not left on REST.

5. Reuse (after the proof)

• media_resource (media.ts:68, org-keyed, nullable org): same filter; teacher-only sync (learner keeps signed-URL delivery). The media_assignment join table is offering-scoped and rides the existing offeringMembershipFanOut — independent of this filter.
• teacher-side cat_pool read (cat_pool.ts, org-keyed, not-null org): cat_pool is already admin-authored/readwrite on the admin rail. Give teachers a read of published pools either by widening its customWhere to branch teacher claims through this filter (the offering claims-branch precedent) or by an ADR-0027 read projection with this filter. Recommend widening the existing entry (one filter, symmetric) over a second shape.

6. Open decisions (need Anton)

A. Who may WRITE an org-keyed grade_category — an authorization decision, resolved entirely in RLS on the sync path (no REST in any option). The READ filter (§3.2, four branches) lets an offering-only teacher (offering grant, no org grant) read their offering’s org reference data so they can apply categories to their line items. The question is only whether they may also author org-level categories. Options — all sync: (i) Recommended — read via all four branches; WRITE RLS admits org-admin + org-teacher only. Offering-only teachers read and apply categories but do not author org-level ones (authoring is an org-level action over an org-shared resource). This is read-broad / write-org — the same read-only-consumption shape learners already have for discussion/offering_resource, not a foot-gun. No REST. (ii) Full write parity — add an org-keyed teacher-manage WRITE policy that also admits an offering-teacher via exists (select 1 from offering where offering.organization_id = row.organization_id and offering.id in (<their offering grants>)) (a normal Postgres RLS policy — full SQL, not the Electric grammar). Offering-teachers author too, but this grants org-wide write to an offering-only teacher (a privilege widening over org-shared rows). Sync, no REST — choose only if product wants offering-teachers authoring org categories. (iii) Narrow the READ to org-grants only (drop branch 4) for exact read=write symmetry; offering-only teachers then neither read nor author org reference data. Sync, no REST. Recommendation: (i).

B. Global (null-org) default rows. grade_category/media_resource allow organization_id IS NULL (platform-global defaults). The literal-IN matches no NULL, so without a disjunct these sync only to platform admins. Options: include an organization_id is null disjunct so every teacher reads the global defaults (includeGlobalNull: true) — recommended for shared reference data — or keep them platform-only. Note the benign asymmetry already accepted for catalog_resource (platform admin can edit null-org rows via the rail; REST blocks all).

7. What this is NOT

• Not a pgxsinkit change — customWhere + the proven subquery grammar cover it; this is emergent registry/RLS work only.
• Not the offering-scoped (a)s (media_assignment, external_package_assignment, learning_unit_external_link, cat_pool_assignment) — those are already unblocked via offeringMembershipFanOut and need no new filter.
• Not the Wave-3 (b) authoring tables — those ride POST /api/mutations/unit, a separate effort.