Scheme operations: bulk enrollment & Slack
Schema deep-dive · living document · #19 in the reading sequence
| Tables | group_scheme.bulk_enrollment_jobs[1], group_scheme.slack_workspaces[2], group_scheme.slack_member_links[3] |
| Owner service | group-scheme-service (sole writer) |
| Locator | BMJ-YYYY-NNNNNN (bulk jobs). The two Slack tables have no locator - they key on Slack-native natural keys (team_id; composite (team_id, slack_user_id)), see §3/§9 |
| Last updated | 2026-08-21 |
| Companion | Group Schemes (narrative) · previous: Scheme & roster |
1. Scope and usage
These three tables are the operational edges of the scheme domain - the two things an employer scheme does at scale that the roster tables (Scheme & roster) do not model. One is bulk enrollment: an employer hands over a list of employees and the service enrols them asynchronously, one policy at a time, tracking progress in bulk_enrollment_jobs. The other is the Slack integration: an employer installs the Olly Slack app into their workspace, and members can be reached (and can consent to a health-chat) over Slack DMs - slack_workspaces binds the workspace to a scheme, slack_member_links binds a Slack user to an Olly party.
They share a schema, an owner service and an admission surface (both live behind the same cluster-only /internal API, §2), but nothing else. The bulk job is a fire-and-forget progress counter; the Slack tables are a durable binding store. This page covers them together because each is a second-ring member of the scheme story too small for its own page and too distinct to fold into the roster page.
All three are built ahead of the volume that will use them, and the headroom is concrete. bulk_enrollment_jobs already tracks total / processed / failed_count plus a per-row errors jsonb, so an employer onboarding a thousand employees gets progress and per-member failure reasons out of the model that exists; what gets added is resumability and a durable member hand-off (§9). slack_workspaces and slack_member_links already key on Slack-native ids and hold the bot token as an OpenBao path rather than a credential, so the tenth workspace install is the same insert as the first, with no per-tenant schema change and no secret in this database (§8, §9).
Live population as of 2026-08-21: bulk_enrollment_jobs holds 10 rows, every one from a single afternoon of e2e runs on 2026-07-10 (six COMPLETED, four FAILED); slack_workspaces and slack_member_links hold exactly one row each, both from a single demo install on 2026-07-21. The paths run end to end and have not yet carried production load, so §9 gives both the extension points and the four defects worth fixing before they do.
2. Boundaries and relationships
| These tables are not… | That concern lives in | Join |
|---|---|---|
| the roster row | group_scheme.scheme_members - the bulk job causes roster rows to gain a policy_locator, it never is one[7] | bulk_enrollment_jobs.scheme_id FK; per-member UpdateMemberPolicyLocator |
| the policy issuance | enrollment - the bulk worker calls enrollment's internal Flow-0 issue route per member[15]; the emitted POL- is copied back onto the roster row | HTTP, policyLocator in response |
| the OAuth handshake | identity - it drives the Slack app-install callback and only then persists the binding here over /internal/slack[19] | HTTP, team_id |
| the bot token | OpenBao - slack_workspaces stores only the Bao path, never the secret[17] | bot_token_bao_path (a path) |
| the member (party) | policy_admin.parties; slack_member_links.party_locator points at it, unverified | PTY- soft ref |
| the health-chat | triage / health-chat owns the DM-backing session; chat_id is a copy of its uuid[18] | TEXT uuid, no FK |
The whole internal surface is cluster-only. Every bulk and Slack route described here is either JWT-gated (the public bulk-start) or behind the shared X-Internal-Service secret, which fails closed - a 503 when the secret is unset, a constant-time-compared 401 on mismatch[20]. Identity's Slack bot is a trusted caller with no employer session, which is why these endpoints exist alongside the employer-JWT ones rather than in place of them.
3. Structure
DDL[1][2][3] · Go models[10][17][18]
bulk_enrollment_jobs
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK, internal |
locator | text | ✓ | UNIQUE. BMJ-YYYY-NNNNNN (not SCH-; own minter, §3 below) |
scheme_id | uuid | ✓ | Real FK to schemes(id) (in-service); indexed |
status | text | ✓ | Default 'PENDING'; PENDING → RUNNING → COMPLETED/FAILED (§5); convention, no CHECK; indexed |
total | int | ✓ | Default 0. Member count in the request |
processed | int | ✓ | Default 0. Members attempted (success or fail) |
failed_count | int | ✓ | Default 0. Members that errored |
errors | jsonb | ✓ | Default '[]'; array of {member, error} - but overwritten to null on clean completion, see §9 |
created_at / updated_at | timestamptz | ✓ | now() defaults |
slack_workspaces
| Field | Type | Req | Notes |
|---|---|---|---|
team_id | text | ✓ | PK = the Slack team id (natural key, no locator) |
scheme_locator | text | ✓ | The SCH- this workspace belongs to; indexed; soft ref (cross-service, and even the in-schema slack_member_links do not FK it) |
bot_token_bao_path | text | ✓ | A path into OpenBao, e.g. secret/slack/workspaces/<team> - the token itself never lands here (§8) |
scopes | text | ✓ | Default ''. Granted OAuth scopes, comma-joined (users:read,users:read.email) |
status | text | ✓ | Default 'ACTIVE'; the only other value is 'UNINSTALLED' (tombstone) |
installed_at / updated_at | timestamptz | ✓ | now() defaults |
slack_member_links
| Field | Type | Req | Notes |
|---|---|---|---|
team_id | text | ✓ | Half of the composite PK (team_id, slack_user_id) |
slack_user_id | text | ✓ | The other half - the Slack user id (U…) |
party_locator | text | ✓ | The Olly member's PTY-; indexed; soft ref |
chat_id | text | ✓ | Default ''. The health-chat session uuid backing this DM thread |
consent_at | timestamptz | Nullable - NULL until the member explicitly consents; the one nullable column on this table | |
created_at / updated_at | timestamptz | ✓ | now() defaults |
Field-by-field: what and why
bulk_enrollment_jobs.locator - minted BMJ-<year>-<seq %06d> by a process-local atomic.Int64 counter[4], not the shared locators.Next per-prefix Postgres sequence the scheme itself uses for SCH-. The prefix in the header table reflects the code (BMJ-), not the BEJ- one might guess from the table name. The counter is seeded at boot from MAX(locator) in the table so a single-pod restart continues the sequence rather than re-minting BMJ-YYYY-000001 into the unique index - the fix for #1428, whose comment records that the counter used to start at zero on every boot and increment non-atomically[12][13], wired in main.go right before the service is constructed[14]. This is the same independent-reimplementation wart the Claim page notes for claims.claims_locator_seq: a per-service minter beside the shared one. It fixes restart collisions but not horizontal scale - two replicas hold two counters and would still race (§9).
The three counters (total / processed / failed_count) - the whole point of the table. total is set once at creation from the request length; processed and failed_count are driven by the worker loop, one increment per member, and written back with the status by a single column-scoped UpdateProgress[9]. processed counts attempts, not successes - a failed member still increments it, so processed == total at the end and failed_count carries how many of those attempts errored[7]. Successes are processed - failed_count, never stored.
errors jsonb - an array of {member, error} objects, one per failed member, marshalled from the worker's accumulated slice at the end[8]. The DDL declares it NOT NULL DEFAULT '[]', and the RUNNING mark writes a literal [] - but a job that finishes with no errors marshals a nil Go slice, which encoding/json renders as the scalar null, so COMPLETED rows store jsonb null (§6, §9). NOT NULL is satisfied (jsonb null is not SQL NULL), but the "always an array" reading the default implies is not.
slack_workspaces.bot_token_bao_path - the load-bearing security decision. The column is a reference to the secret, not the secret: the model comment states plainly "the bot token itself is not stored here - only the Bao path where it lives"[17], and the live value confirms it - a path (secret/slack/workspaces/T0AB24AH62Z), not a token (§6). group-scheme never reads OpenBao at all; a grep for "bao" across the service finds only this column, its migration, and the JSON field. Whoever needs the actual token (identity, health-chat) resolves the path against Bao itself. A leaked slack_workspaces dump therefore exposes bindings, not credentials.
slack_workspaces.status - ACTIVE | UNINSTALLED. Uninstall is a tombstone, not a delete: the app-uninstall webhook flips status to UNINSTALLED via a targeted UPDATE[27], so a later reinstall can rebind the same team_id and the row's history survives. The rebind guard keys off this: a team ACTIVE on a different scheme is refused, but a tombstoned row is fair game (§8).
slack_member_links.consent_at - the only nullable column across all three tables, and deliberately so. A link is created (or its chat_id rotated) without consent; consent is a separate, later stamp via a dedicated endpoint[25], and the link's upsert path is written to never overwrite it - the ON CONFLICT update touches only party_locator, chat_id, updated_at, leaving consent_at and created_at untouched so a re-link can never silently revoke a prior consent[24][30].
4. Invariants
| Invariant | Enforced by |
|---|---|
bulk_enrollment_jobs.locator unique | DB UNIQUE[1] |
| A bulk job belongs to a real scheme | DB FK scheme_id → schemes(id) (in-service)[1] |
BMJ- locator format + no restart collision | Application: atomic counter seeded from DB MAX(locator) at boot[13][14] - single-pod only (§9) |
| A retried member enrols once, not twice | Application: enrollment issue is scoped an idempotency key of (party, scheme)[15][16] |
| One workspace binding per Slack team | DB PK on team_id[2] |
| A team cannot be silently re-bound to a new scheme while ACTIVE | Application: pre-check + ON CONFLICT … WHERE race-guard + re-read → ErrSchemeConflict (409)[22][21] |
| One link per (team, Slack user) | DB composite PK (team_id, slack_user_id)[3] |
| A re-link never revokes consent | Application: ON CONFLICT update excludes consent_at / created_at[24] |
| Only a trusted cluster caller writes these bindings | Application: X-Internal-Service guard, fail-closed[20] |
status vocabularies (job / workspace) | Convention only - no CHECK on any of the three tables |
scheme_locator / party_locator / chat_id point at real rows | Nothing - stated as expectation, enforced nowhere (soft refs, cross-service and even in-schema) |
errors is always a JSON array | Nothing - the default says [], the write path can store null (§9) |
| Row changes captured to CDC | Debezium publication dbz_group_scheme covers all three (live \d) |
5. Lifecycle
The bulk job is a four-state machine. StartBulkEnrollment inserts it PENDING and stashes the member list; RunJob marks it RUNNING (resetting the counters and errors to []), loops the members, then writes a terminal status[5][7]:
The terminal branch is the subtle part: FAILED means all-or-nothing failure - failedCount == len(members), or an empty list; any other outcome is COMPLETED[8]. So COMPLETED does not mean "everyone enrolled"; it means "the job ran to the end", and failed_count may be non-zero under it. On the terminal write the worker emits a keyed bulk_enrollment.completed / .failed event to group-scheme.events carrying the counts[8][29].
The Slack binding chain has no single status column to diagram; it is a sequence across identity and group-scheme, install → link → consent → uninstall:
Each hop is a handler on the /internal/slack group[19]: workspace upsert[21], the roster-match passthrough[28], link upsert[23], consent stamp[25], and the uninstall tombstone[27]. The roster-match passthrough exists because identity's Slack bot has no employer-admin JWT for the public members route; it reuses ListMembers verbatim but projects the result down to a four-field identity-only DTO so demographics / postcode never cross that wire[28].
6. Populated example
All rows are real and carry no free-text PII (locators, Slack ids and a session uuid only); nothing is redacted.
A bulk job, walked - BMJ-2026-000004 (FAILED)
{
"locator": "BMJ-2026-000004",
"scheme_id": "…uuid…",
"status": "FAILED",
"total": 3, "processed": 3, "failed_count": 3,
"errors": [
{"member": "PTY-2026-000063", "error": "enrollment: POST http://enrollment:8080/internal/policies/issue returned 400: {\"error\":\"missing Idempotency-Key header\"}"},
{"member": "PTY-2026-000064", "error": "…same…"},
{"member": "PTY-2026-000065", "error": "…same…"}
],
"created_at": "2026-07-10T12:01:37Z",
"updated_at": "2026-07-10T12:01:37Z"
}| Key | Read by | What actually happens |
|---|---|---|
total: 3 | set once at creation | three members were in the request[5] |
processed: 3 = total | the worker loop | all three were attempted; the loop increments processed on failure too[7] |
failed_count: 3 = total | terminal-status logic | all failed ⇒ FAILED, not COMPLETED[8] |
errors[].error | operators / the .failed event | enrollment's issue route rejected the call for a missing Idempotency-Key - a failure the current code path fixed, see below |
This row is a fossil of the pre-fix path. Its sibling BMJ-2026-000001/002/003 failed a different way (create quote: … POST /quotes returned 404), because bulk enrolment once created a quote before issuing; today the worker calls enrollment's internal issue route directly and wraps each call in an idempotency key of member-scheme[15] - the exact header these 2026-07-10 rows died without. The six later rows that afternoon (BMJ-2026-000005…000010) all COMPLETED with failed_count = 0.
The errors = null defect, live. Those six COMPLETED rows do not store []; they store jsonb null - SELECT jsonb_array_length(errors) errors with "cannot get array length of a scalar" on them. The clean-completion path marshals a nil Go slice, which becomes null[8], and UpdateProgress writes it verbatim[9]. The DEFAULT '[]' only ever applies to a row nobody updates.
The Slack install, walked
{
"team_id": "T0AB24AH62Z",
"scheme_locator": "SCH-2026-001126",
"bot_token_bao_path": "secret/slack/workspaces/T0AB24AH62Z",
"scopes": "users:read,users:read.email",
"status": "ACTIVE",
"installed_at": "2026-07-21T08:53:30Z"
}| Key | Read by | What actually happens |
|---|---|---|
team_id (PK) | every Slack lookup | the workspace's natural key - no SCH--style locator was minted (§9) |
scheme_locator | GetSlackWorkspaceByScheme | binds this Slack workspace to one Olly scheme; indexed for the reverse lookup |
bot_token_bao_path | identity / health-chat, against Bao | confirmed a path, not a token - resolves in OpenBao; group-scheme never dereferences it[17] |
status: ACTIVE | the rebind guard | a second scheme trying to claim T0AB24AH62Z while this is ACTIVE gets a 409[22] |
The one linked member, walked - the consent gap in time
{
"team_id": "T0AB24AH62Z",
"slack_user_id": "U0AL5TNUPAT",
"party_locator": "PTY-2026-000121",
"chat_id": "96ade9af-c54d-413d-be40-17cece7e47ff",
"created_at": "2026-07-21T13:14:20Z",
"consent_at": "2026-07-21T13:37:56Z"
}The link was created at 13:14 with consent_at NULL, then consent was stamped 23 minutes later at 13:37 by the separate consent endpoint[25][26] - created_at and updated_at differ, and the two timestamps demonstrate the two-step "link first, consent later" design directly. chat_id is the health-chat session uuid the DM thread runs on; party_locator resolves this Slack user to a real member.
Live population for context (2026-08-21): bulk_enrollment_jobs 10 rows (6 COMPLETED, 4 FAILED, none PENDING/RUNNING - the transient states are never observed at rest); slack_workspaces 1 (ACTIVE); slack_member_links 1 (consented).
7. Who references these tables
| Where | Column / mechanism | Meaning there |
|---|---|---|
group_scheme.schemes | bulk_enrollment_jobs.scheme_id FK | which scheme the bulk job enrolled into (only real FK here) |
group_scheme.scheme_members | bulk worker's UpdateMemberPolicyLocator | a completed job back-fills each member's policy_locator[7] |
| employer app / ops | GET /api/v1/bulk-enrollments/{jobLocator}/status[6] | poll a job's counters; also the bulk_enrollment.completed/failed event |
group_scheme.schemes | slack_workspaces.scheme_locator (soft) | which scheme a Slack workspace belongs to |
| identity Slack bot | GET /internal/slack/workspaces/by-team/{teamID} | resolve a DM's team to its scheme + Bao token path |
slack_workspaces | slack_member_links.team_id (soft, no FK) | the workspace a member link lives under - a soft ref even within the schema (§9) |
policy_admin.parties | slack_member_links.party_locator (soft) | the Olly member a Slack user maps to |
| triage / health-chat | slack_member_links.chat_id | the session backing the Slack DM thread |
Only bulk_enrollment_jobs.scheme_id is a real FK; every other reference is a locator / natural-key / topic soft ref (cross-service rule, and here even one in-schema case).
8. Design determinations
- The bot token is stored as an OpenBao path, never the token.
bot_token_bao_pathis a secret reference; group-scheme holds no Slack credential and never calls Bao. A dump of this table leaks bindings, not access[17][2]. - The Slack tables key on Slack-native identifiers, not Olly locators.
slack_workspacesPKs onteam_id,slack_member_linkson(team_id, slack_user_id)- a deliberate departure from theXXX-YYYY-NNNNNNscheme every other table uses (§9). The keys the external system already guarantees unique are the keys, so no locator is minted for an entity Olly does not originate[2][3]. - Uninstall is a tombstone, rebind is guarded. A team ACTIVE on one scheme cannot be silently re-bound to another (409
ErrSchemeConflict); an UNINSTALLED row can be reclaimed. The guard is defensive to the point of a pre-check plus anON CONFLICT … WHERErace clause plus a re-read, because the pre-check is not atomic with the write[22][27]. - Consent is durable and never silently revoked. The link upsert excludes
consent_at/created_at, so re-linking (e.g. achat_idrotation) cannot wipe a recorded consent[24]. - The whole surface is cluster-only and fails closed. Bulk-start is JWT-gated; every Slack binding route is behind
X-Internal-Servicewith a 503-when-unset, constant-time-compare posture[20]. - Bulk intake is asynchronous (202), and the job is the only durable record.
POST …/bulk-enrollmentsreturns202 Acceptedwith the PENDING job; the counters persist, but the in-flight member list does not - it lives in an in-memory map, so a pod restart mid-job strands it (TODO #1164: abulk_job_memberstable)[6][11]. - The bulk locator is minted in-service, not via the shared package. An
atomic.Int64seeded fromMAX(locator)at boot (#1428), the same independent-reimplementation the Claim page flags forclaims- and, being per-process, it protects restarts but not horizontal scale (§9)[12][14]. - Per-member enrolment is idempotent on
(party, scheme). A retried job replays rather than double-issuing, the fix the historicalBMJ-2026-000004died without[15]. That idempotency is what makes a retry-the-failed-members flow safe to add (§9). - Both subsystems are modelled ahead of the volume that uses them. The job's counters and per-member
errors, and the Slack tables' natural keys plus Bao-path indirection, are in place before employer onboarding at volume and multi-workspace installs arrive - so each of those is wiring on top of the existing tables rather than a migration (§9).
The Slack work landed across four commits on 2026-07-20/21 as the tasks of a "Slack team-import plan" (workspace store, member links + uninstall tombstone, the internal roster passthrough, and the narrow-DTO fix); no ADR / issue number is attached in the history, so none is cited here.
9. Caveats and extensibility
Group and individual. Both subsystems are group-only by construction, and correctly so. Bulk enrolment is defined as "an employer hands over a list" and every job hangs off a scheme_id FK; a direct-to-consumer member enrols one policy through the ordinary path and never touches this table. The Slack integration binds a workspace to a scheme - the unit is an employer's Slack team, which an individual does not have. Neither table needs a nullable "individual" mode; the individual has no bulk job and no Slack workspace. This is the mirror image of the roster page's point: the group-specific machinery is isolated to group-specific tables, so the individual path costs nothing.
Extension points - when, what, where.
| When we need … | What to add | Where |
|---|---|---|
| employers to onboard at volume | The job model already carries the shape: total / processed / failed_count written by a column-scoped UpdateProgress, a per-member errors jsonb, a 202 Accepted intake, and terminal bulk_enrollment.completed / .failed events on group-scheme.events. What gets added is durability of the work list - persist the in-flight members so a restart re-drives a RUNNING job rather than stranding it. RunJob already names the target table (bulk_job_members, TODO #1164) | services/group-scheme-service/internal/service/bulk_enrollment.go - the TODO #1164 seam[11]; new table via services/group-scheme-service/migrations/ (next after 0007_codify_group_contract_columns.sql) |
| to retry only the members that failed | The counters distinguish attempts from failures and errors carries a per-member reason, so the input to a re-drive is already stored. Add an endpoint that reads errors and re-issues those members; per-member issue is idempotent on (party, scheme), so a replay cannot double-issue a policy | bulk_enrollment.go (RunJob[7], idempotency key[15]); route in services/group-scheme-service/internal/handler/bulk_enrollment.go[6] |
| more Slack workspaces to install | The binding store is multi-tenant by construction: slack_workspaces PKs on Slack's team_id, slack_member_links on (team_id, slack_user_id), so the keys the external system already guarantees unique are the keys - no locator to mint, no per-tenant table. bot_token_bao_path holds a path into OpenBao rather than the token, so a new install adds a reference and no Slack credential enters this database. Rebind guard, uninstall tombstone and the consent stamp all run today; the tenth install exercises the same code as the first | no schema change - services/group-scheme-service/internal/handler/internal_slack.go[19] and internal/repository/gorm_scheme.go[22] |
| Slack member matching beyond a demo roster | identity matches a DM's email against the roster through the /internal/slack passthrough, which projects the result to four identity-only fields so demographics and postcode stay off that wire. Broadening the match (secondary emails, name fallback) is a change to that projection and its caller, not to these tables | internal_slack.go roster passthrough + slackRosterMember DTO[28] |
consent to reconcile with olly-consent | consent_at is a local stamp that the link upsert deliberately never overwrites, which keeps the DM path's consent check a single-row read. A service-of-record integration writes the consent record in olly-consent and keeps this column as the fast local copy | consent handler internal_slack.go[25], gorm_scheme.go[26] |
Stated plainly, not defects. The two Slack tables carry no XXX-YYYY-NNNNNN locator - a deliberate consequence of keying on the external system's identifiers (§8), with the practical cost that a support engineer addresses a Slack binding by team_id rather than a locator, and tooling that assumes a locator column finds none here. slack_member_links.team_id is a soft reference even in-schema: it belongs to slack_workspaces.team_id with no FK, so deleting a workspace row would orphan its links - which the tombstone design sidesteps by never deleting. Job and workspace status vocabularies live in Go comments and model tags with no CHECK behind them[10].
Known defects, with the fix:
errorsstores the scalarnull, not[], on clean completion. The DDL default and the RUNNING write both say[], but a no-error finish marshals a nil Go slice, whichencoding/jsonrenders asnull, andUpdateProgresswrites it verbatim.SELECT jsonb_array_length(errors)then throws "cannot get array length of a scalar" on the six live COMPLETED rows, so any query or dashboard treating the column as an array breaks on exactly the successful jobs. Fix: marshal an initialised empty slice (or coalesce to'[]'on the write) so the column is always an array. Where:services/group-scheme-service/internal/service/bulk_enrollment.goterminal-status logic[8] andinternal/repository/gorm_bulk_enrollment.go[9] (§6).- A restart mid-job strands the row in RUNNING. The member list is handed to the worker through an in-memory map, so a pod restart loses it while the row keeps
status = RUNNINGwith counters frozen part-way - no code re-drives or fails it, and a poller waits on a job nothing will finish. No live row is in that state. Fix: persist the members (bulk_job_members) and re-drive or fail RUNNING jobs at boot. Where:bulk_enrollment.gopendingMembers/ TODO #1164[11], plus the new table inservices/group-scheme-service/migrations/. COMPLETEDdoes not mean every member enrolled. The terminal branch calls a job FAILED only whenfailedCount == len(members)or the list was empty; any partial failure lands as COMPLETED with a non-zerofailed_count. A caller that polls for COMPLETED and reports success to an employer is wrong whenever one employee failed. Fix: either emit a distinct terminal state for partial failure, or make the status route and thebulk_enrollment.completedpayload force the caller to readfailed_count. Where: terminal-status logic inbulk_enrollment.go[8] and the status handler[6] (§5).- The
BMJ-minter is per-process, so it breaks under horizontal scale. #1428 made the counter atomic and seeded fromMAX(locator)at boot, which fixes single-pod restarts, but two replicas hold two independent counters and would mint the same locator into the unique index - the second insert fails and the employer's job is refused. The service has only run on one pod, so this has not fired. Fix: mint from the shared per-prefix Postgres sequence (locators.Next) the scheme itself uses forSCH-, retiring the in-service counter. Where:nextBulkJobLocator[4] andSeedBulkJobLocator[13] inbulk_enrollment.go(§3).
References
Code links are pinned to commit b61c5802 on main (2026-08-21); the file is the anchor if lines drift. Pins are checked mechanically by docs/site/scripts/check-code-refs.py.
group-scheme-service/migrations/0001_create_schema.sql#L31-bulk_enrollment_jobsDDL; UNIQUE locator L33, scheme FK L340005_slack_workspace.sql#L2-slack_workspacesDDL; PK team_id L3, bao-path column L50006_slack_member_link.sql#L2-slack_member_linksDDL; composite PK L10internal/service/bulk_enrollment.go#L62-nextBulkJobLocator:BMJ-%d-%06dbulk_enrollment.go#L135-StartBulkEnrollment: create PENDING + stash membersinternal/handler/bulk_enrollment.go#L18- start handler (202) + status routebulk_enrollment.go#L190-RunJob: RUNNING mark, per-member loop,UpdateMemberPolicyLocator, countersbulk_enrollment.go#L258- terminal-status logic (all-fail/empty ⇒ FAILED), nil-slice →null, keyed eventsinternal/repository/gorm_bulk_enrollment.go#L32-UpdateProgresscolumn-scoped writeinternal/repository/repository.go#L60-BulkEnrollmentJobmodel, status vocabulary commentbulk_enrollment.go#L86-pendingMembersin-memory hand-off; restart-loses-members + TODO #1164bulk_enrollment.go#L21- #1428 comment: atomic counter replacing boot-zero re-mintbulk_enrollment.go#L44-SeedBulkJobLocator: seed fromMAX(locator), loud on parse failurecmd/server/main.go#L114-SeedBulkJobLocatorwired at boot before the service is builtbulk_enrollment.go#L227- idempotency keymember-scheme; internalIssuePolicyReqinternal/client/policy_admin_client.go#L44-WithIdempotencyKeycontext helperrepository.go#L75-SlackWorkspacemodel; "bot token itself is not stored here - only the Bao path" L75-76repository.go#L89-SlackMemberLinkmodel; chat_id = health-chat session, consent_at nullableinternal/handler/internal_slack.go#L21-mountInternalSlackRoutes: the whole/internal/slacksurfaceinternal/handler/internal_schemes.go#L30-internalServiceGuard: fail-closed 503, constant-time compare 401internal_slack.go#L182- workspace upsert handler; 409 on cross-scheme rebindinternal/repository/gorm_scheme.go#L117-UpsertSlackWorkspace: pre-check +ON CONFLICT … WHERErace guard + re-readinternal_slack.go#L111- member-link upsert handlergorm_scheme.go#L217-UpsertSlackMemberLink:ON CONFLICTexcludes consent_at / created_atinternal_slack.go#L160- consent handler: stampconsent_at=now, 404 if no linkgorm_scheme.go#L242-SetSlackMemberLinkConsenttargeted UPDATEgorm_scheme.go#L189-TombstoneSlackWorkspace: status → UNINSTALLED, not deleteinternal_slack.go#L42- roster-match passthrough + narrowslackRosterMemberDTO (keeps demographics off the wire)internal/kafka/producer.go#L96-PublishWithKey(bulk terminal events ongroup-scheme.events)repository.go#L129-SchemeRepositorySlack method contracts; consent-preserving upsert comment
Live-schema facts (columns, indexes, the three tables' Debezium coverage in dbz_group_scheme, goose history showing 0005/0006 applied 2026-07-20/21, the job status/counter census, the jsonb null on COMPLETED rows, the worked bulk job's errors array, the one Slack workspace's bao path, and the linked member's two consent timestamps) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d group_scheme · \d group_scheme.bulk_enrollment_jobs, \d group_scheme.slack_workspaces, \d group_scheme.slack_member_links, select … from goose_db_version, 2026-08-21.