Admin sync client — full build plan

Status: build-ready plan (2026-06-29). Companion to wave-2-sync-migration-disposition.md. That document identified “no admin-console sync client” as structural prerequisite #1 — the thing that blocks every admin-authored entity (lti_tool_* mappings, catalog_resource write, the cat_pool family, identity_binding, organization/achievement entity_image) from reaching the sync rail. This document is the complete, code-level plan to build that subsystem and prove it end-to-end with lti_tool_context_mapping as the first admin entity.

It is written so the work can be executed step by step without re-deriving the current state. Every “current state” claim below is cited to a file and line that was read while writing this plan.

As-built status (2026-06-30)

BUILT on develop in two commits. Two deltas from the plan below were decided during implementation (both improve on the plan; recorded here so this stays a living doc):

1. Read-filter sourcing — built from the JWT grants, not a live person_role_grant subquery. The plan (§2.3, §4 Step B) recommended a live person_role_grant subquery for revocation-immediacy. In implementation that needs an effective-window predicate (now()), and now() inside an Electric tagged-subquery where is unconfirmed/risky. Since the write path (adminGrantExists) already sources authority from the JWT grants (which the auth hook pre-filters to active), sourcing the read filter from the same JWT grants (readOrganizationAdminOrgIds) makes read/write symmetric, avoids now(), and moots prerequisite-#2-style concerns. Org-ids are injected as escaped UUID literals inside a self-contained offering→org subquery (the pgxsinkit buildGrantScopeShapeWhere approach, since Electric can’t bind a list as one $n). buildAdminOfferingOrgRowFilterWhere lives in packages/db/src/schema/authorization.ts.

2. Step H — the AdminSyncProvider is mounted and the LTI panel is now FULLY cut over (the read-shape follow-on below was completed). Initially the panel read the rail as a REST-enriched overlay because the synced lti_tool_context_mapping row carries only FK ids while the list displays joined fields and the create form resolves issuer + client_id + deployment_id → deployment_row_id. The follow-on resolved that by putting the supporting tables on the rail:

   • lti_tool_platform + lti_tool_deployment → readonly admin sync entries (any-admin read filter, buildAnyAdminRowFilterWhere); offering → its shared claims-branching customWhere widened to admit admins (platform → all; org admin → their org’s offerings), backward-compatible for non-admins (a revision bump triggers a one-time learner/teacher resync). All three added to adminSyncRegistry.
   • The panel now reads a sync-native client-side join (enrichSyncedContextMappings) of mapping + deployment + platform + offering, and creates via the sync mutation after resolving the deployment client-side from the synced registrations (resolveDeploymentRowId). REST remains the not-ready / offline fallback (and the component-test path) via useAdminSyncOptional.
   • The org-scope primitives were centralized in row-filter-claims.ts (buildAdminOrganizationRowFilterWhere for direct-org tables, buildAnyAdminRowFilterWhere, buildOrganizationAdminInClause) — this also delivers disposition prerequisite #2 (the reusable org-scoped read filter) for the remaining Wave-2 org-scoped reference data.

Everything else landed as planned: claim readers, the admin-manage RLS builder, the defineSyncTable conversion, adminSyncRegistry + authoritative wiring, createAdminSyncClient + adminReadModelNames, the applier branch, the full migration regen, and an integration test (7/7 on real Postgres: platform-admin attach/re-map/detach with creator+audit stamped; org-admin scoped; cross-org and grantless blocked by RLS). pgxsinkit needed no functional change (§3.1).

---

1. What exists today vs. what is missing

1.1 The teacher rail (the thing we mirror)

The teacher sync subsystem is the proven template. Its end-to-end shape:

Layer	Location	What it does
Authoritative registry	packages/db/src/sync-registry.ts:97-100	authoritativeSyncRegistry = defineSyncRegistry({...readonlyAuthoredEntries, ...readwriteEntries}) — the single source the server (Electric proxy + apply fn + governance) is generated from.
Per-client registry	packages/db/src/sync-registry.ts:129-137	teacherSyncRegistry = defineSyncRegistry({ discussion, groupSet, group, groupMembership, offeringResource }) — the teacher’s projection.
Read-contract guard	packages/db/src/sync-registry.ts:142-143	assertReadContractPreserved(authoritativeSyncRegistry, teacherSyncRegistry, { label: "teacher" }).
Read-model view names	packages/offline-data/src/sync-registry.ts (teacherReadModelNames)	Maps each writable entry to its _read_model overlay view name for pglite.query.
Client factory	packages/offline-data/src/sync-client.ts:49-86	createTeacherSyncClient(options, factory = createSyncClient) → SyncClient<TeacherSyncRegistry>; dataDir idb://emergent-teacher-sync-v1.
React provider	apps/teacher-portal/src/features/sync/lib/sync.tsx:257-599	TeacherSyncProvider owns the single client, runs ready/flush, polls, exposes typed mutations + assembled reads through useTeacherSync().
Mount point	apps/teacher-portal/src/main.tsx:40-45	One app-level <TeacherSyncProvider> wraps <RouterProvider>.
Env helpers	apps/teacher-portal/src/lib/env.ts:18-25	getSyncMutationsUrl() → <supabase>/functions/v1/mutations; getElectricProxyUrl() → <supabase>/functions/v1/electric-proxy.
Auth token	apps/teacher-portal/src/features/auth/lib/client.ts:17	getTeacherAccessToken from createSupabaseBrowserAuthClientBindings.
Apply path	apps/api/src/sync-mutation-applier.ts	createPostgresMutationApplier() → per-table payload builder + executeArtifactMutation → pgxsinkit_apply_mutations.

1.2 The admin rail (what we are adding)

apps/admin-console is REST-only today:

• No sync/Electric/PGlite provider. The provider tree is EmergentMantineProvider → EmergentI18nProvider → AdminAuthProvider → AppRouter (apps/admin-console/src/main.tsx:42-51). There is no @emergent/offline-data usage and no adminSyncRegistry.
• Auth exists. getAdminAccessToken is exported from apps/admin-console/src/features/auth/lib/client.ts:15, built from createSupabaseAuthBindings() and reading VITE_SUPABASE_URL / VITE_SUPABASE_PUBLISHABLE_KEY.
• No sync env helpers. admin-console has no getElectricProxyUrl / getSyncMutationsUrl equivalent; it builds REST URLs inline (e.g. lti-tool/lib/client.ts:265-272 …/functions/v1/workflow/lti-tool/context-mappings).
• Vite is already prepared. apps/admin-console/vite.config.ts reads env from repo root (envDir: "../../") and the workbox denylist already excludes /electric/.

So the admin rail is: a registry + claim readers + a read filter + an admin RLS builder + a client factory + a provider + env helpers + the first entity + an applier branch + a migration + a test.

---

2. Key technical determinations (with evidence)

These four findings shape every design choice below. They were verified against both repos.

2.1 Admin grants live in a queryable Postgres table — AND in the JWT

There is a real table person_role_grant (packages/db/src/schema/authorization.ts:18-66):

person_role_grant(
  id uuid pk,
  person_id uuid -> person (cascade),
  role authorization_role_enum,          -- learner|teacher|assistant|observer|mentor|organization_admin|platform_admin
  organization_id uuid -> organization,  -- null for platform_admin
  offering_id uuid -> offering,          -- null except offering-scoped roles
  effective_from timestamptz not null,
  effective_until timestamptz,           -- null = no expiry
  created_by_person_id uuid -> person,
  ...auditColumnsUs()
)

A Supabase auth hook refresh_person_identity_claims() (packages/db/src/.../authorization_claim_projection.ts) reads the active grants for a person via build_authorization_claim_payload() and writes auth.users.raw_app_meta_data → app_metadata.authorization = { version, personId, grants: [...] }. Triggers refresh it on every person_role_grant INSERT/UPDATE/DELETE. The grant JSON shape minted into the JWT:

// platform admin
{ "role": "platform_admin",     "scope": { "kind": "platform" } }
// organization admin
{ "role": "organization_admin", "scope": { "kind": "organization", "organizationId": "<uuid>" } }
// teacher at offering
{ "role": "teacher",            "scope": { "kind": "offering", "offeringId": "<uuid>", "organizationId": "<uuid>" } }

Consequence: the read filter has two correct sources of truth for “which orgs does this principal administer” — (A) a live subquery against person_role_grant, or (B) the grants array already in the JWT. We choose (A) — see §2.3.

2.2 The WRITE-path RLS is already admin-aware

packages/db/src/schema/claim-aware-policies.ts reads the JWT inside Postgres via current_setting('request.jwt.claims') (readClaimsJsonSqlText(), lines 125-130) and already has:

• adminGrantExists(organizationExpression) (lines 190-206) — EXISTS over jsonb_array_elements(app_metadata.authorization.grants) matching platform_admin/platform OR organization_admin/organization with scope.organizationId = <orgExpr>.
• buildClaimAwareOwnerOrAdminNativePolicies() (lines 250-263) — owner OR admin, 4 commands.
• buildClaimAwareTeacherManagePredicate() (lines 273-319) — platform_admin OR org_admin OR teacher/assistant at org/offering scope.

So the admin write authority predicate (adminGrantExists) already exists; we only need a thin builder that emits the 4 native policies (select/insert/update/delete) keyed solely on it (§4.3).

2.3 The READ path: customWhere is Electric-evaluated; subqueries work, array params don’t

shape.rowFilter.customWhere has signature (claims: JwtClaims, params?) => string | SQL | null (pgxsinkit/packages/contracts/src/config.ts:287-312). It is compiled by buildRowFilterShape (lines 363-380): a returned Drizzle SQL is run through pgDialect.sqlToQuery(...), producing the Electric shape where string plus bound $n params. null → no filter (all rows); DENY_ALL (sql\false``) → no rows.

Three capabilities, confirmed in pgxsinkit source:

1. Uncorrelated subquery — YES. pgxsinkit’s own board-schema does exactly this (pgxsinkit/packages/board-schema/src/registry.ts:36-68):

   function memberTeams(sub: string) {
     return sql`select ${c(teamMember.teamId)} from ${teamMember} where ${c(teamMember.userId)} = ${sub}`;
   }
   // team row filter:
   return sql`${c(team.id)} in (${memberTeams(claims.sub)})`;

   emergent’s offeringMembershipFanOut is the same shape. The subquery is evaluated by Electric’s Postgres-backed shape engine; the single scalar (sub/personId) is the bound $1.

2. List from JWT claims — YES, via a JS-resolved literal IN (...). pgxsinkit ships resolveGrantScopeIds(claims, { scopeKind, roleValues, grantsClaimPath }) + buildGrantScopeShapeWhere(column, ids) (pgxsinkit/packages/contracts/src/supabase-rls.ts:556-603). This resolves the org-id list in JS from app_metadata.authorization.grants and injects a literal "col" in ('a','b') (escaped), "1 = 0" when empty. This is the JWT-snapshot alternative.

3. Array as one bound param — NO. col IN ($1) where $1 is an array does not work; Electric’s where grammar binds scalars only. (This is an Electric limit, not a pgxsinkit one.)

Decision — use the live subquery (capability 1) against person_role_grant. Reasons:

• It mirrors the existing emergent read filters (offeringMembershipFanOut, groupMembershipManageOrOwnFanOut) — one consistent pattern.
• It reflects grant revocation immediately on the next shape reconcile, rather than waiting for a JWT refresh (the grants-in-JWT snapshot lags until the token is re-minted).
• It needs no new claim plumbing beyond the personId we already read.

2.4 JwtClaims is a loose object — grants are readable today without a pgxsinkit change

jwtClaimsSchema = z.looseObject({ sub?, app_metadata?: looseObject({ roles? }) }) (pgxsinkit/packages/contracts/src/config.ts). Because it is a loose object, unknown keys pass through at runtime. emergent’s existing readPersonId() (packages/db/src/schema/row-filter-claims.ts:14-22) already reads app_metadata.person_id — a field not in pgxsinkit’s JwtClaims type — via a narrow typed reader. We read grants the same way. So no pgxsinkit type change is required to read grants in a customWhere.

---

3. pgxsinkit changes

Per the cross-repo rule (augment pgxsinkit generically; emergent adapts later — see feedback_pgxsinkit_foundation_electric_subqueries and the “fix upstream first” rule in CLAUDE.md), this section states exactly what, if anything, must change in pgxsinkit.

3.1 Functionally required: NONE

The admin sync feature is fully expressible with pgxsinkit as published today:

• Read filter → uncorrelated subquery in customWhere (board-schema proves the pattern).
• Grants readable at runtime → JwtClaims is looseObject.
• Write authority → emergent’s own adminGrantExists (already in claim-aware-policies.ts).
• Apply path → pgxsinkit_apply_mutations(jsonb, text, bool, bool, jsonb) already sets request.jwt.claims + role for RLS (pgxsinkit/packages/server/src/mutations/plpgsql-apply.ts:316-363), and the authClaim managed-field strategy already stamps created_by_person_id from app_metadata.person_id (buildManagedFieldExpression, lines 46-54).

So nothing in pgxsinkit blocks this work. Build it in emergent now.

3.2 Recommended upstream improvements (DX/generality — do these in pgxsinkit, then re-pin)

These are genuine gaps in pgxsinkit’s ergonomics for the admin/role-scope case. They belong upstream because they are generic sync capabilities, not emergent specifics. None blocks emergent; each removes a cast or a hand-rolled helper that every consumer would otherwise duplicate.

#	Change	Where (pgxsinkit)	Why upstream	Priority
P1	Type the authorization claims. Extend jwtClaimsSchema so app_metadata.person_id?: string and app_metadata.authorization?: { version?, personId?, grants?: Array<{ role?, scope?: { kind?, organizationId?, offeringId? } }> } are typed (still loose).	packages/contracts/src/config.ts (jwtClaimsSchema)	Every Supabase consumer reads person_id/grants from claims today by casting through the loose object. Typing them removes the cast in readPersonId, the new admin readers, and resolveGrantScopeIds. Backwards-compatible (only adds optional fields).	High (small, pure win)
P2	Ship a role-scoped subquery row-filter helper. A buildRoleScopeSubqueryWhere({ scopeColumn, grantTable, personColumn, roleColumn, scopeIdColumn, roles, personId, includeAllForRoles }) that returns the scopeColumn IN (select scopeIdColumn from grantTable where personColumn = $1 and roleColumn = any(roles) and active) SQL, plus an “platform/global sees all” escape.	packages/contracts/src/supabase-rls.ts (next to resolveGrantScopeIds / buildSupabaseMembershipNativePolicies)	pgxsinkit already has the JWT-list helper (resolveGrantScopeIds/buildGrantScopeShapeWhere) and the membership-policy helper, but no live-subquery role-scope helper — which is the form emergent (and any RLS-backed app) actually wants for read filters that must reflect revocation. Generalises offeringMembershipFanOut-style filters.	Medium
P3	Pair an admin/role-scope RLS-policy builder with the row filter. A buildSupabaseRoleScopeNativePolicies(...) mirroring buildSupabaseMembershipNativePolicies but matching platform_admin/organization_admin-style grants, so the write policy and the read filter come from one declaration and cannot drift.	packages/contracts/src/supabase-rls.ts	Read filter (Electric) and write policy (Postgres RLS) must agree on authority. Today emergent hand-writes both (adminGrantExists for RLS; a bespoke subquery for the filter). A paired helper keeps them in lockstep upstream.	Medium
P4	(Optional) Document the array-param limitation in the customWhere JSDoc: “a variable-length list must be a subquery or a JS-resolved literal IN (...); a single $n cannot bind an array (Electric grammar).”	packages/contracts/src/config.ts (RowFilterSpec.customWhere doc)	This footgun cost real investigation time; documenting it next to the type saves the next consumer.	Low

Sequencing of the pgxsinkit work: P1 is a tiny, pure-additive type change worth doing first and re-pinning (bun run dev:bump) so emergent’s readers are typed from the start. P2/P3 are larger and can land after emergent has the bespoke versions working — emergent’s bespoke row-filter-claims.ts helper (§4.2) becomes the reference implementation that P2/P3 generalise, then emergent collapses onto the upstream helper in a follow-up (same pattern as the authUid → authClaim collapse, project_authclaim_and_teacher_discussion_sync). P4 ships with P1.

Note: emergent consumes @pgxsinkit/* from the @dev channel during this work (bun run dev:link / dev:bump); the public npm release is a separate, later CL step. P1–P4 do not need to be released to npm before emergent’s admin rail is built — emergent can build against the bespoke in-repo helpers and adopt the upstream helpers when they land on @dev.

---

4. emergent build plan (ordered)

Each step lists the file(s), the exact change, and a code sketch. Steps A–G are backend + registry (verifiable with a single integration test against real Postgres). Step H is the admin-console UI cutover. Steps I–K are migration, test wiring, and validation.

Step A — Admin claim readers

File: packages/db/src/schema/row-filter-claims.ts (currently 40 lines: readPersonId, buildPersonOwnerRowFilterWhere, re-exports c / DENY_ALL).

Add typed readers over the loose claims object (mirroring readPersonId’s narrow-cast style):

// The authorization grants minted into app_metadata by refresh_person_identity_claims().
// Read defensively from the loose JwtClaims object (pgxsinkit P1 will type this upstream).
interface AuthorizationGrantClaim {
  role?: string;
  scope?: { kind?: string; organizationId?: string; offeringId?: string };
}

function readAuthorizationGrants(claims: JwtClaims): AuthorizationGrantClaim[] {
  const appMetadata = (claims as { app_metadata?: { authorization?: { grants?: unknown } } }).app_metadata;
  const grants = appMetadata?.authorization?.grants;
  return Array.isArray(grants) ? (grants as AuthorizationGrantClaim[]) : [];
}

export function isPlatformAdmin(claims: JwtClaims): boolean {
  return readAuthorizationGrants(claims).some((g) => g.role === "platform_admin" && g.scope?.kind === "platform");
}

isPlatformAdmin is the only claims-derived signal we need in JS — it lets the filter return null (all rows) for a platform admin, avoiding a redundant subquery. Org-admin scoping is done entirely in SQL against person_role_grant (Step B), so it always reflects live grants.

Step B — Admin/org-scoped read-filter helper

File: packages/db/src/schema/row-filter-claims.ts (same file).

Add a fan-out builder that scopes rows to the organizations the principal administers, parameterised on the synced table’s organization expression. It subqueries person_role_grant live.

import { personRoleGrantTable } from "./authorization";

// Rows whose organization the caller administers (organization_admin), OR everything for a
// platform_admin. The subquery reads live person_role_grant — revocation takes effect on the next
// shape reconcile, not on JWT refresh. `c()` keeps refs bare (Electric grammar); $1 = personId.
export function buildAdminOrganizationRowFilterWhere(organizationColumn: AnyColumn) {
  return (claims: JwtClaims): SQL => {
    if (isPlatformAdmin(claims)) {
      return ALLOW_ALL; // null — no filter; platform admin syncs every row
    }
    const personId = readPersonId(claims);
    if (personId === null) {
      return DENY_ALL;
    }
    return sql`${c(organizationColumn)} in (
      select ${c(personRoleGrantTable.organizationId)}
      from ${personRoleGrantTable}
      where ${c(personRoleGrantTable.personId)} = ${personId}
        and ${c(personRoleGrantTable.role)} = 'organization_admin'
        and ${c(personRoleGrantTable.effectiveFrom)} <= now()
        and (${c(personRoleGrantTable.effectiveUntil)} is null
             or ${c(personRoleGrantTable.effectiveUntil)} >= now())
    )`;
  };
}

For lti_tool_context_mapping, whose own org is reached through its offering, provide a variant that scopes by the offering’s organization (reusing offeringOrganizationExpression, the helper added for offering_resource in packages/db/src/schema/catalog-resource.ts):

// lti_tool_context_mapping has offering_id; the org is offering.organization_id. Scope by the
// offerings whose org the caller administers (platform_admin → all).
export function buildAdminOfferingOrgRowFilterWhere(offeringColumn: AnyColumn) {
  return (claims: JwtClaims): SQL => {
    if (isPlatformAdmin(claims)) return ALLOW_ALL;
    const personId = readPersonId(claims);
    if (personId === null) return DENY_ALL;
    return sql`${c(offeringColumn)} in (
      select ${c(offeringTable.id)} from ${offeringTable}
      where ${c(offeringTable.organizationId)} in (
        select ${c(personRoleGrantTable.organizationId)} from ${personRoleGrantTable}
        where ${c(personRoleGrantTable.personId)} = ${personId}
          and ${c(personRoleGrantTable.role)} = 'organization_admin'
          and ${c(personRoleGrantTable.effectiveFrom)} <= now()
          and (${c(personRoleGrantTable.effectiveUntil)} is null
               or ${c(personRoleGrantTable.effectiveUntil)} >= now())
      )
    )`;
  };
}

ALLOW_ALL is null typed for the builder’s return. Confirm whether pgxsinkit exports an ALLOW_ALL sentinel; if not, return null directly (the customWhere contract treats null as “no filter”). DENY_ALL (sql\false`) is exported from @pgxsinkit/contracts` and already re-used here.

Revision discipline: these filters are new (no prior revision), but record the rule in a comment — any later edit to the authorization logic must bump shape.rowFilter.revision on the entry, or the client serves a stale shape (config.ts:RowFilterSpec.revision).

Step C — Admin-manage RLS policy builder

File: packages/db/src/schema/claim-aware-policies.ts.

adminGrantExists(orgExpr) (lines 190-206) already encodes “platform_admin OR org_admin for org”. Add a thin builder that emits the four native policies keyed solely on it, mirroring buildClaimAwareOwnerOrAdminNativePolicies (lines 250-263) but without the owner column:

export interface ClaimAwareAdminManageOptions {
  name: string; // policy name prefix
  organizationExpression: SQL; // the row's organization (or offering→org) expression
  commands?: PolicyCommand[]; // default ["select","insert","update","delete"]
}

// Admin-only manage: platform_admin (any org) or organization_admin for the row's org. No owner
// branch — admin authoring tables have no per-person owner gate. USING + WITH CHECK both = adminGrantExists.
export function buildClaimAwareAdminManageNativePolicies(options: ClaimAwareAdminManageOptions): PgPolicy[] {
  const predicate = adminGrantExists(options.organizationExpression);
  const commands = options.commands ?? ["select", "insert", "update", "delete"];
  return commands.map((command) =>
    pgPolicy(`${options.name}_${command}`, {
      as: "permissive",
      for: command,
      to: authenticatedRole,
      using: command === "insert" ? undefined : predicate,
      withCheck: command === "select" || command === "delete" ? undefined : predicate,
    }),
  );
}

(Match the exact using/withCheck per-command convention used by the existing builders in this file — read buildClaimAwareOwnerOrAdminNativePolicies and copy its command/clause handling verbatim rather than the sketch above, which is illustrative.)

For lti_tool_context_mapping the organizationExpression is the offering’s org: offeringOrganizationExpression(table.offeringId) (the helper from catalog-resource.ts).

Step D — Convert lti_tool_context_mapping to a sync entry

File: packages/db/src/schema/lti-tool.ts (table at lines 86-113; secret-free, as are all its siblings — confirmed; secrets live only in the lti_tool-schema storage tables, none synced).

The table has ...auditColumnsUs() but not the LWW protocol columns. Convert it the same way offeringResourceSyncEntry was built (packages/db/src/schema/catalog-resource.ts):

1. Swap ...auditColumnsUs() → ...auditAndLwwColumnsUs() (adds lastOperationId, lastApplySequence) inside a makeLtiToolContextMappingColumns() factory.
2. Replace the pgTable(...) with defineSyncTable(...):

export const ltiToolContextMappingSyncEntry = defineSyncTable({
  tableName: "lti_tool_context_mapping",
  makeColumns: makeLtiToolContextMappingColumns,
  mode: "readwrite",
  conflictPolicy: "last-write-wins",
  shape: {
    rowFilter: {
      // Admin sees the mappings for offerings in the org(s) they administer; platform_admin sees all.
      customWhere: buildAdminOfferingOrgRowFilterWhere(/* columns.offeringId */),
    },
  },
  clientProjection: { omitColumns: ["lastOperationId", "lastApplySequence"] },
  governance: {
    managedFields: [
      {
        column: "createdByPersonId",
        applyOn: ["create"],
        strategy: "authClaim",
        claimPath: ["app_metadata", "person_id"],
      },
      { column: "createdAtUs", applyOn: ["create"], strategy: "nowMicroseconds" },
      { column: "updatedAtUs", applyOn: ["create", "update"], strategy: "nowMicroseconds" },
    ],
  },
  extras: (table) => [
    // existing unique + indexes, rewritten against the defineSyncTable column refs
    uniqueIndex("lti_tool_context_mapping_deployment_context_uq")
      .on(table.deploymentRowId, table.contextId)
      .where(sql`${table.resourceLinkId} is null`),
    uniqueIndex("lti_tool_context_mapping_deployment_context_resource_link_uq")
      .on(table.deploymentRowId, table.contextId, table.resourceLinkId)
      .where(sql`${table.resourceLinkId} is not null`),
    index("lti_tool_context_mapping_offering_idx").on(table.offeringId),
    index("lti_tool_context_mapping_created_by_idx").on(table.createdByPersonId),
    ...buildClaimAwareAdminManageNativePolicies({
      name: "lti_tool_context_mapping_admin_manage",
      organizationExpression: offeringOrganizationExpression(table.offeringId),
    }),
  ],
});

// Keep a Drizzle table export for non-sync server code that still queries it directly.
export const ltiToolContextMappingTable = ltiToolContextMappingSyncEntry.table;

createdByPersonId becomes an authClaim managed field, so it must NOT be in the create payload (the apply fn stamps it). The created_at_us/updated_at_us columns are stamped by nowMicroseconds. This matches offering_resource exactly.

Audit every other importer of ltiToolContextMappingTable — the NRPS/AGS/launch services reference it. They keep working via the re-exported .table, but verify column names are unchanged (they are; only LWW columns are added).

Step E — adminSyncRegistry + authoritative wiring

File: packages/db/src/sync-registry.ts.

1. Import the entry: import { ltiToolContextMappingSyncEntry } from "./schema/lti-tool";
2. Add an adminAuthoredEntries group and fold it into the authoritative registry (the single source the proxy + apply fn + governance are generated from):

// Admin-authored content (Wave-2 admin rail): synced to the admin console only. Authoritative-readwrite
// here (the admin write rail); no learner/teacher projection (these tables are not in those registries).
const adminAuthoredEntries = {
  ltiToolContextMapping: ltiToolContextMappingSyncEntry,
};

export const authoritativeSyncRegistry = defineSyncRegistry({
  ...readonlyAuthoredEntries,
  ...readwriteEntries,
  ...adminAuthoredEntries,
});

// The admin console's projection (ADR-0025): the admin authors these on the sync write rail.
export const adminSyncRegistry = defineSyncRegistry({
  ...adminAuthoredEntries,
});

assertReadContractPreserved(authoritativeSyncRegistry, adminSyncRegistry, { label: "admin" });
export type AdminSyncRegistry = typeof adminSyncRegistry;

Because the admin entry is the authoritative entry (same object, readwrite), the read-contract guard passes trivially. The admin table is intentionally absent from learnerSyncRegistry / teacherSyncRegistry.

Verify the Electric proxy + apply-fn + governance generators iterate authoritativeSyncRegistry (the teacher agent confirmed “the Electric proxy + apply function are generated from the authoritative registry”). Adding to adminAuthoredEntries → authoritative is therefore what makes the server serve and apply the new table. Also confirm the proxy does not maintain a per-client table allowlist that would need lti_tool_context_mapping added — if shapes are authorised purely by the per-table customWhere, a non-admin requesting the shape simply gets DENY_ALL (no rows), which is safe. (Check apps/api/.../electric-proxy-routes.ts.)

Step F — offline-data: client factory + read-model names + tests

File: packages/offline-data/src/sync-client.ts — add createAdminSyncClient, a verbatim copy of createTeacherSyncClient (lines 49-86) with registry: adminSyncRegistry and dataDir idb://emergent-admin-sync-v1:

export type AdminSyncClient = SyncClient<AdminSyncRegistry>;

export interface CreateAdminSyncClientOptions {
  electricUrl: string;
  writeUrl: string;
  getAuthToken?: () => Promise<string | null | undefined>;
  syncEnabled?: boolean;
  dataDir?: string;
  onStatusChange?: (status: SyncRuntimeStatus) => void;
}

export async function createAdminSyncClient(
  options: CreateAdminSyncClientOptions,
  syncClientFactory: SyncClientFactory = createSyncClient,
): Promise<AdminSyncClient> {
  const writeUrl = normalizeUrl(options.writeUrl, "writeUrl");
  const electricUrl = normalizeElectricShapeUrl(options.electricUrl);
  return await syncClientFactory({
    registry: adminSyncRegistry,
    electricUrl,
    writeUrl,
    ...(options.getAuthToken ? { getAuthToken: async () => (await options.getAuthToken?.()) ?? undefined } : {}),
    syncEnabled: options.syncEnabled ?? true,
    dataDir: options.dataDir ?? "idb://emergent-admin-sync-v1",
    ...(options.onStatusChange ? { onStatusChange: options.onStatusChange } : {}),
  });
}

File: packages/offline-data/src/sync-registry.ts — add adminReadModelNames:

export const adminReadModelNames = {
  ltiToolContextMapping: getViewConfig(adminSyncRegistry.ltiToolContextMapping.view!).name,
} as const;

File: packages/offline-data/src/index.ts — export createAdminSyncClient, AdminSyncClient, CreateAdminSyncClientOptions, adminSyncRegistry, AdminSyncRegistry, adminReadModelNames.

File: packages/offline-data/src/sync-registry.test.ts — this suite asserts exact key lists. Add: adminSyncRegistry import; an adminReadModelNames toEqual assertion; and (since ltiToolContextMapping joins the authoritative registry) confirm whether any existing Object.keys(authoritativeSyncRegistry)-style assertion exists and extend it. This is the test that bit the offering_resource commit twice — grep the whole file for every exact-list assertion before running, and update each.

Step G — Apply path

File: apps/api/src/sync-mutation-applier.ts. Mirror the offering_resource branch (assertOfferingResourceOrgConsistency + buildOfferingResourceArtifactPayload + applyOfferingResourceMutation, lines 1290-1376):

1. Add "lti_tool_context_mapping" to writableSyncTableNames (Set at lines 74-114).
2. Add it to the executeArtifactMutation table-name union (lines 397-412).
3. Add the dispatch branch after offering_resource (lines 1759-1801).
4. Implement:

function buildLtiToolContextMappingArtifactPayload(input: MutationApplyInput): Record<string, unknown> {
  const { mutation } = input;
  const payload = readMutationPayload(mutation.payload);
  const entityId = resolveEntityId(mutation.entityKey);

  if (mutation.kind === "create") {
    const deploymentRowId = readPayloadString(payload, "deploymentRowId", "deployment_row_id");
    const contextId = readPayloadString(payload, "contextId", "context_id");
    const offeringId = readPayloadString(payload, "offeringId", "offering_id");
    if (!deploymentRowId || !contextId || !offeringId) {
      throw createMutationApplyError(
        "lti_tool_context_mapping create requires deploymentRowId, contextId and offeringId.",
        "failed",
        400,
      );
    }
    return {
      id: entityId,
      deployment_row_id: deploymentRowId,
      context_id: contextId,
      resource_link_id: readPayloadString(payload, "resourceLinkId", "resource_link_id") ?? null,
      offering_id: offeringId,
      // created_by_person_id, *_at_us are managed fields — NOT sent here.
    };
  }
  if (mutation.kind === "update") {
    const updatePayload: WireNode = {};
    const resourceLinkId = readPayloadString(payload, "resourceLinkId", "resource_link_id");
    if (resourceLinkId !== undefined) updatePayload.resource_link_id = resourceLinkId;
    return updatePayload;
  }
  return {};
}

async function applyLtiToolContextMappingMutation(sql: SqlClient, input: MutationApplyInput): Promise<void> {
  // Optional business guard: assert the deployment + offering exist (parallel to
  // assertOfferingResourceOrgConsistency). Authority (admin-of-org) is enforced by the RLS WITH CHECK.
  const payload = buildLtiToolContextMappingArtifactPayload(input);
  await executeArtifactMutation(sql, input, "lti_tool_context_mapping", payload);
}

The apply fn already sets request.jwt.claims/role for RLS and stamps authClaim/nowMicroseconds managed fields (§2.2, §3.1) — no change needed there.

Step H — admin-console: env + provider + mount + cutover

File (new): apps/admin-console/src/lib/env.ts — copy apps/teacher-portal/src/lib/env.ts verbatim (getSyncMutationsUrl, getElectricProxyUrl over VITE_SUPABASE_URL).

File (new): apps/admin-console/src/features/sync/lib/sync.tsx — AdminSyncProvider + useAdminSync(), modeled on TeacherSyncProvider (apps/teacher-portal/.../sync.tsx:257-599):

• useAdminAuth() → personId; build client in an effect when authenticated.
• createAdminSyncClient({ electricUrl: getElectricProxyUrl(), writeUrl: getSyncMutationsUrl(), getAuthToken: () => getAdminAccessToken(), dataDir: \idb://emergent-admin-sync-v1-${personId}`, onStatusChange })`.
• await client.ready; await client.flush(); then read via client.pglite.query over adminReadModelNames.ltiToolContextMapping; poll on an interval; expose typed mutations (createContextMapping, updateContextMapping, deleteContextMapping) that call client.tables.ltiToolContextMapping.create/update/delete.
• Destroy on unmount / sign-out (copy the teacher cleanup exactly).

File: apps/admin-console/src/main.tsx:42-51 — wrap the router in <AdminSyncProvider> inside <AdminAuthProvider> (sync needs auth):

<AdminAuthProvider>
  <AdminSyncProvider>
    <AppRouter />
  </AdminSyncProvider>
</AdminAuthProvider>

File: apps/admin-console/package.json — add "@emergent/offline-data": "workspace:*" (it already has @emergent/domain, @emergent/ui; offline-data brings @pgxsinkit/client).

Cutover: the LTI context-mappings panel (apps/admin-console/src/features/lti-tool/) currently GETs/POSTs …/functions/v1/workflow/lti-tool/context-mappings (lib/client.ts:265-272, 826-831). Switch its reads to useAdminSync().contextMappings and its writes to the sync mutations, gated on sync.isReady (exactly how GroupingTab/DiscussionsTab fall back to REST until sync.isReady). Keep the REST GET as the not-ready fallback. The create/update REST route can be retired in a follow-up once the sync write path is proven in the UI (same staged approach as the discussion REST retirement, project_api_sync_conversion Wave-1) — do not retire it in the same commit as the cutover.

Step I — Migrations

Full-regenerate per docs/runbooks/regenerate-migrations.md (the same procedure used for offering_resource): rm -rf packages/db/src/migrations/*/; bunx drizzle-kit generate … --name initial_baseline; then the ordered custom generators (authorization-claim-projection, identity-linking, auth-hooks, governance, sync-function); scaffold + restore the two byte-exact hand-written customs (storage_buckets, credential_signing_role); then the remaining policy generators. Verify:

• lti_tool_context_mapping now has last_operation_id + last_apply_sequence columns in the baseline.
• The admin-manage RLS policies appear (4 policies) on the table.
• The table appears in the sync-function (apply-fn) and governance outputs.
• migration-drift + sync-registry tests pass.

This is the agent’s job (file generation); Anton’s live-DB reset is optional/non-blocking — integration tests run against a fresh Podman Postgres that applies the regenerated migrations.

Step J — Integration test

File (new): tests/integration/lti-context-mapping-sync-cycle.integration.test.ts, modeled on tests/integration/offering-resource-sync-cycle.integration.test.ts. Fixtures: the seeded teacher’s offering/org, a platform-admin person + grant, an org-admin person + grant for that org, a second org + an org-admin for it, and an LTI deployment row. Tests (direct applier via createPostgresMutationApplier()):

1. Platform admin attaches a mapping → acked; row lands; created_by_person_id stamped from app_metadata.person_id; created_at_us/updated_at_us populated. Update + delete round-trip.
2. Org admin of the offering’s org attaches → acked (RLS WITH CHECK via adminGrantExists passes).
3. Org admin of a different org is blocked → no row (RLS denies).
4. Grantless principal blocked → no row (parallel to the offering_resource grantless test).

(Read-path scoping is via Electric’s customWhere, not direct Postgres SELECT — same note as the offering_resource test: there is no learner/non-admin SELECT policy to assert against directly; the write-guard is what’s Postgres-testable.)

File: package.json — add the new test to test:integration:compose after offering-resource-sync-cycle.integration.test.ts.

Step K — Validate + commit

bun run typecheck, then commit via background Bash (the pre-commit validate runs the turbo-affected lane; never --no-verify; watch for the flaky sealFlowState test and re-run if it bounces). One commit for the backend + registry + migration + test (Steps A–G, I, J), a second for the admin-console provider + cutover (Step H), keeping the UI change reviewable on its own.

---

5. Test matrix

Layer	Test	Asserts
Registry	packages/offline-data/src/sync-registry.test.ts	adminSyncRegistry keys; ltiToolContextMapping.mode === "readwrite"; adminReadModelNames; authoritative includes the entry; assertReadContractPreserved passes (module-eval).
Governance	registry test	ltiToolContextMapping managed fields include createdByPersonId authClaim + createdAtUs/updatedAtUs nowMicroseconds; projection omits lastOperationId/lastApplySequence.
RLS (unit)	claim-aware-policies test	the table declares 4 admin-manage policies (select/insert/update/delete), all permissive.
Apply (integration)	lti-context-mapping-sync-cycle.integration.test.ts	platform-admin + org-admin attach/round-trip; cross-org + grantless blocked; creator/audit stamped.
Migration	migration-drift test	baseline contains LWW columns + policies + apply-fn + governance entry.
UI (manual / e2e later)	—	admin console reads mappings from sync when isReady, falls back to REST otherwise; writes go through sync.

---

6. Risks & open decisions

1. Proxy table authorisation. Confirm electric-proxy-routes authorises shapes by per-table customWhere only (safe: non-admins get DENY_ALL) and has no per-client table allowlist needing the new table added. (Step E note.)
2. person_role_grant visibility to the Electric shape engine. The subquery in the read filter runs against person_role_grant; verify the shape engine’s DB role can read it (it is in public; the existing offering_membership fan-out already subqueries a public table, so this should be equivalent — but confirm there is no RLS on person_role_grant that would empty the subquery under the shape engine’s role).
3. Authority symmetry. Write authority reads grants from the JWT (adminGrantExists); read authority subqueries person_role_grant live. These can diverge for the window between a grant change and a JWT refresh (read reflects it sooner). This is acceptable and arguably desirable, but note it. pgxsinkit P3 (paired helper) would make the two come from one declaration.
4. First-entity scope choice. lti_tool_context_mapping is scoped by its offering’s org. If a simpler proof is wanted first, a platform-admin-only entity (filter = isPlatformAdmin ? null : DENY_ALL, no org subquery) would exercise the rail with less SQL — but lti_tool_context_mapping is the cleanest real admin entity and exercises the org-scope path we need for the rest, so it is the recommended first cut.
5. REST retirement timing. Keep the context-mappings REST route until the sync UI write path is proven; retire in a follow-up (do not bundle with the cutover).

---

7. Sequencing & rough size

1. pgxsinkit P1 (type the claims) — small, upstream, re-pin via dev:bump. Optional-but-first.
2. emergent backend + registry + migration + integration test (Steps A–G, I, J) — one focused commit; this is where the novel pieces are (claim readers, read filter, admin RLS builder, first entity, applier). Verifiable entirely against real Postgres.
3. emergent admin-console provider + env + cutover (Step H) — second commit; first sync provider in the admin console.
4. pgxsinkit P2/P3 (role-scope subquery filter + paired RLS builder) — upstream generalisation of the bespoke row-filter-claims.ts helper; emergent collapses onto it in a follow-up.
5. Remaining admin-authored entities — once the rail exists, lti_tool_ags_line_item_mapping, catalog_resource write, the cat_pool family (with learner-exclusion), identity_binding follow the same template (per the disposition doc sequencing §4).

The backend increment (1–2) is the high-value, fully-verifiable core. The UI cutover (3) and the upstream generalisation (4) are independent follow-ons.