Provider credentialing & reviews
Schema deep-dive · living document · #18 in the reading sequence
| Tables | provider.credentialing_requests[1], provider.provider_reviews[2] - the two satellites of the providers directory |
| Owner service | provider (sole writer) |
| Locators | CRD-YYYY-NNNNNN (credentialing) · reviews have no locator - a uuid PK only, not externally addressable |
| Last updated | 2026-08-21 |
| Companion | ERD story, slide 10 · Providers (narrative) · previous: Provider (#10) |
1. Scope and usage
These two tables are the satellites of the provider directory. A credentialing request is the onboarding/vetting workflow that decides whether a clinician or organisation is admitted to the network; a provider review is the member-facing quality signal on the profile. Both hang off providers by a real in-service foreign key[1][2] and neither has a life outside that provider.
Credentialing is the doorway onto the network list - "insurers have developed hospital networks, i.e. a list of hospitals with which the insurer has contracts" - and approval is the one event that flips a provider's network_status to ACTIVE[10]. Reviews are the clinician-profile feature (#1108): a rating and a comment that aggregate onto the profile read as an average and a count[15].
Both write paths are built and exercised; the content is fixture. The model already carries what a real credentialing operation needs: a status lifecycle with a SUBMITTED-only decision guard, a reviewed_at stamp, a free-text decision rationale, and the one code path that admits a provider to the network. On the review side, rating is range-enforced by the database itself, and the average is computed at read time so there is no denormalised value to drift. A reviewer workflow with a real admin surface, a decision history, and rating-aware provider matching are each a named change on top of these fields rather than a re-model (§9).
Live population as of 2026-08-21: credentialing holds 61 rows - 23 SUBMITTED, 19 APPROVED, 19 REJECTED - and each of the 38 decisions carries the reviewer note e2e approve or e2e reject, written by the e2e suite against the live service. provider_reviews holds 11 rows across 6 providers, all seeded, all ratings 4 or 5 (average 4.64).
2. Boundaries and relationships
| A credentialing request / review is not… | That concern lives in | Join |
|---|---|---|
| the provider directory or the network flag itself | provider.providers - identity, contact, network_status. Approval flips that flag in a second UPDATE on the providers row, it does not store it here[10] | provider_id FK |
| an audit trail of the decision | not modelled here - the request row is mutated in place with a GORM Save[14], so it holds current state rather than a ledger. History arrives as an append-only satellite alongside it, the shape claims.claim_events already uses (§9) | none |
| a verified identity of the reviewer | not modelled here - review author is free text with no party_id, no JWT, no moderation state; whoever calls the endpoint names themselves[16] | none |
| a ranking input for search | reviews aggregate onto the profile read only[17]; the nearest-provider search ranks purely by distance and filters on ACTIVE - it never reads rating[18] | none |
| the network gate at settlement | claims never consults credentialing or network_status (see Provider §3) - the gate is enforced at booking, not adjudication | none |
| a cross-service event contract | the provider.events Kafka topic - credentialing and review changes are announced by a best-effort direct publish from the handler, not a transactional outbox[22] | Kafka topic |
3. Structure
credentialing_requests
| Field | Type | Req | Notes |
|---|---|---|---|
id / locator | uuid / text | ✓ | PK / UNIQUE CRD-YYYY-NNNNNN |
provider_id | uuid | ✓ | Real FK to providers (in-service)[1]; indexed |
status | text | ✓ | Default 'SUBMITTED'; SUBMITTED | APPROVED | REJECTED (Go enum[5], no CHECK) |
submitted_at | timestamptz | ✓ | Default now(); always stamped |
reviewed_at | timestamptz | Stamped on the approve/reject decision; NULL while SUBMITTED (*time.Time in the model) | |
reviewer_notes | text | Free-text decision rationale. Nullable in DDL, but never NULL in practice - the Go field is a non-pointer string, so a SUBMITTED insert writes '' (see §9) | |
created_at | timestamptz | ✓ | Default now() |
provider_reviews
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK. No locator - reviews are not externally addressable |
provider_id | uuid | ✓ | Real FK to providers[2]; indexed |
author | text | ✓ | Free-text display name; no party link, no moderation |
rating | int | ✓ | DB CHECK 1..5[2] - a rare DB-enforced invariant on this platform (see §4) |
comment | text | Free text; optional | |
created_at | timestamptz | ✓ | Default now() |
Field-by-field: what and why
credentialing_requests.status - the load-bearing column. It is text with a DEFAULT 'SUBMITTED' and no CHECK constraint; the three legal values live only in the Go CredentialingStatus enum[5]. The legal transitions are enforced in code, not the schema: both ApproveCredentialing and RejectCredentialing refuse to act on anything that is not currently SUBMITTED, returning a 409[10][11]. There is no re-open path and no in-place history: the decision is a Save over the same row[14], so the request is a current state, not a ledger.
reviewer_notes - the decision rationale, and a ''-vs-NULL trap. It is text (nullable) in the migration[1], but the Go model declares ReviewerNotes string (a non-pointer)[3], so every GORM insert that has no note writes the Go zero value '', not NULL. The submit handler never sets it, so all 23 SUBMITTED rows carry reviewer_notes = '' (verified live: 0 NULL, 23 empty). The 38 decided rows carry a real (here, test) string. This is the same ''-vs-NULL trap the Party and Provider pages document - a column that looks consistently populated because the ORM refuses to leave it NULL.
locator on credentialing - CRD-2026-000060. Minted from a one-row counter table, not a Postgres SEQUENCE: credentialing_locator_seq is the sibling of provider_locator_seq[21], and the repository increments it with UPDATE credentialing_locator_seq SET val = val + 1 RETURNING val then formats CRD-%d-%06d by hand[13]. Same odd-one-out pattern as the rest of the provider service (see Provider §3): the row-lock on UPDATE serialises minting, but the table has no PK and relies on holding exactly one row. Live val is 61 and the highest locator is CRD-2026-000061, so the counter and the data agree.
provider_reviews.rating - the platform's rare DB-enforced invariant. The column carries CHECK (rating BETWEEN 1 AND 5)[2], and the add-review handler also validates 1 <= rating <= 5 and returns a 400 before the insert[16]. So rating is guarded twice - application first, DB CHECK as the backstop - where almost every other vocabulary in this estate (status, network_status) is code-only. Because the average is computed at read time there is no denormalised rating column to drift[15].
Where the rating surfaces - GET /providers/{locator} calls reviews.Stats and attaches rating (avg) and reviewCount to the profile response, best-effort (a stats error omits them)[17]. The nearest-provider search does not read rating at all: it filters network_status = 'ACTIVE' and orders by PostGIS distance (location <-> point)[18]. Rating is a display signal on one provider's page, never a ranking input across providers.
4. Invariants
| Invariant | Enforced by |
|---|---|
rating between 1 and 5 | DB CHECK[2] - the rare DB-enforced invariant here - and application pre-validation (400 before insert)[16] |
locator unique (credentialing) | DB UNIQUE[1] |
| Credentialing request / review belongs to a real provider | DB FKs (in-service)[1][2] |
| Credentialing decision only legal from SUBMITTED | Application: approve/reject both refuse any other status with a 409[10][11] |
APPROVED ⇒ provider ACTIVE + activated_at stamped | Application, same handler path, not a transaction: two sequential Updates on two rows[10] (live data is consistent) |
Credentialing status vocabulary | Go enum only[5] - no CHECK on status |
reviewer_notes is meaningful when present | Nothing - nullable in DDL; the non-pointer Go string means SUBMITTED rows hold '', not NULL, and nothing forbids an empty decision note (§9) |
| One review per member per provider | Nothing - no uniqueness beyond the PK; a caller may post unlimited reviews under any author |
| Credentialing decision history preserved | Nothing - the row is Saved in place[14]; there is no events table and no trigger |
| Row changes captured to CDC | Debezium publication dbz_provider (live \d, both tables) |
5. Lifecycle
Credentialing is a synchronous, in-process service flow: approve is one HTTP handler doing two row updates[10], with no Temporal workflow anywhere in the service (see Provider §5 for the stale architecture-page claim to the contrary).
Every arrow is a code method, each stamping reviewed_at and overwriting status/reviewer_notes on the single row:
- submit -
POST /credentialinglooks up the provider by locator, mints aCRD-locator, and inserts aSUBMITTEDrequest[6]. This is also theservice_provider_credentialing_submitMCP tool's route. - approve -
PATCH /credentialing/{locator}/approveguards SUBMITTED, sets APPROVED +reviewed_at+ notes, then in the same call but a separate UPDATE flips the provider to ACTIVE and stampsactivated_at[10]. - reject -
PATCH /credentialing/{locator}/rejectguards SUBMITTED, sets REJECTED +reviewed_at+ notes, and touches only the request[11]. A rejected provider staysPENDINGin the directory (see the Provider page for the "PENDING conflates never-reviewed with reviewed-and-refused" wart).
There is a read-only status shortcut too: GET /credentialing/{npi}/status resolves a provider by NPI and returns its most recent request's status - the route behind the provider_credentialing_status MCP tool[9].
Reviews have no lifecycle. A review is a single INSERT[16]; there is no update, delete, moderation or reply path in the repository or handler. Once written, a row is immutable by absence of any code to change it.
6. Populated example
61 credentialing requests and 11 reviews live. Rows below are real; the free-text reviewer_notes and review comment are shown or redacted per their provenance.
Credentialing, one of each status
// CRD-2026-000001 (the oldest live request, still open)
{ "locator": "CRD-2026-000001", "provider_id": "8882b174-…-5f85adbf9433",
"status": "SUBMITTED", "submitted_at": "2026-04-11T06:06:14Z",
"reviewed_at": null, "reviewer_notes": "" }
// CRD-2026-000006 (an e2e-suite approval)
{ "locator": "CRD-2026-000006", "provider_id": "a1a8a27f-…-8be757725b20",
"status": "APPROVED", "submitted_at": "2026-08-12T14:03:49Z",
"reviewed_at": "2026-08-12T14:03:49Z", "reviewer_notes": "e2e approve" }
// CRD-2026-000007 (an e2e-suite rejection)
{ "locator": "CRD-2026-000007", "provider_id": "7de05eb1-…-8b2b19158a8c",
"status": "REJECTED", "submitted_at": "2026-08-12T14:03:49Z",
"reviewed_at": "2026-08-12T14:03:49Z", "reviewer_notes": "e2e reject" }| Key | Read by | What actually happens |
|---|---|---|
status: SUBMITTED + reviewed_at: null | approve/reject guards | the request is open; a decision is legal only from here[10] |
reviewer_notes: "" on the SUBMITTED row | nothing | the Go zero value, not a NULL - all 23 SUBMITTED rows are identical here (§9) |
reviewer_notes: "e2e approve" / "e2e reject" | display / audit | the column stores whatever the caller sent; every one of the 38 decisions is an e2e-suite write, sub-millisecond submit-to-decide |
status: APPROVED on CRD-2026-000006 | its provider row | the approval flipped that provider to ACTIVE and stamped activated_at in the same call[10] |
status: REJECTED on CRD-2026-000007 | its provider row | nothing on the provider - a rejected provider stays PENDING, never INACTIVE |
Live population: 61 credentialing requests - 23 SUBMITTED, 19 APPROVED, 19 REJECTED (38 rows carry reviewed_at; all 38 notes are e2e approve/e2e reject; the 23 SUBMITTED notes are all ''). The SUBMITTED rows date to April 2026 seed data; the decided rows are August e2e runs.
A review
{
"id": "ae180aae-5458-473f-b0d7-0ddf5095e1a7",
"provider_id": "5a8ae096-…-8601bba9203f",
"author": "Priya N.",
"rating": 5,
"comment": "[redacted free text]",
"created_at": "2026-07-06T09:37:48Z"
}| Key | Read by | What actually happens |
|---|---|---|
author: "Priya N." | display only | a free-text string with no party link - the writer named themselves[16] |
rating: 5 | AVG(rating) on the profile read[17] | contributes to this provider's displayed average; guarded 1..5 by the CHECK |
comment | display only | redacted here per house style; genuine member prose in production would be PII-adjacent |
Live population: 11 reviews across 6 providers, ratings only 4 (×4) and 5 (×7), average 4.64 - a seeded favourable fixture. The profile read returns the latest reviews plus rating + total computed on the fly[15].
7. Who references credentialing & reviews
| Where | Column / mechanism | Meaning there |
|---|---|---|
provider.providers | provider_id FK (both tables) | the parent; approval writes back network_status / activated_at on it[10] |
| provider profile read | reviews.Stats → rating + reviewCount[17] | the member-visible aggregate on GET /providers/{locator} |
| web-admin | credentialing resource (list / show / create)[24] | ops review queue; there is no separate reviews admin resource |
| MCP / platform tools | service_provider_credentialing_submit → POST /credentialing[6]; provider_credentialing_status → GET /credentialing/{npi}/status[9] | submit an onboarding request; check a provider's status by NPI |
provider.events (Kafka) | credentialing.submitted / .approved / .rejected, provider.reviewed[23] | best-effort announcements; no confirmed downstream consumer in-repo |
Neither table is referenced by a foreign key in any other database - the cross-service rule, as everywhere. provider_reviews is not referenced at all outside its own service.
8. Design determinations
- Credentialing is synchronous and in-process - approve/reject are HTTP handlers doing in-place updates; no Temporal saga, no outbox. (§5)
- Approval is the network-entry event - the one code path that flips a provider to
ACTIVE, done as a second UPDATE in the same call rather than a transaction[10]. (§4) - Decisions are current-state, not a ledger -
Saveover the request row, nocredentialing_eventstable[14]; history is not retained. (§2) - Reviews are the clinician-profile feature (#1108) - member-visible rating
- comment, aggregated at read time so there is no denormalised value to drift[15]. (§3)
- Rating is display, not ranking - it rides on one provider's profile read; nearest-provider search ranks by distance and never reads it[18]. (§3)
ratingis DB-CHECK-enforced - the platform's habit is code-only vocabularies; the 1..5 CHECK is a deliberate belt-and-braces on the one field where a bad value would silently skew a public average[2]. (§4)- Events emitted from the handler, best-effort - the semantic
credentialing.*/provider.reviewedproducers were added platform-wide (#1644); provider publishes them directly toprovider.eventsafter the DB write, ignoring the error[22]. (§9) - The model is current-state and additive - decision history, event durability, reviewer identity and review moderation are each a satellite table or a column added beside these two, not a re-model of them. That is why every row in §9 names a migration or a query, and none names a rewrite.
9. Caveats and extensibility
Group and individual. Nothing here is scheme- or policy-aware. Credentialing vets a provider onto the network and reviews rate a provider's profile; both serve group members and (future) individual policyholders identically. The group/individual fork never reaches these tables - it lives entirely in the policy and authorisation layers.
Extension points - when, what, where.
| When we need … | What to add | Where |
|---|---|---|
| credentialing to run a real reviewer workflow | The lifecycle is already modelled: submit inserts SUBMITTED, approve and reject are each guarded to SUBMITTED with a 409, reviewed_at is stamped and reviewer_notes carries the rationale, and approval flips the provider to ACTIVE. What is added is the surface and the identity - an ops screen driving approve/reject, and a reviewed_by column written from the authenticated caller instead of an anonymous decider. Today every decision in the table carries e2e approve / e2e reject and the web-admin resource is list/show/create | ops screen: apps/web-admin/src/app/admin/App.tsx credentialing resource[24]; decision path: services/provider/internal/service/provider.go[10][11]; reviewed_by as a new migration in services/provider/migrations/ |
| decisions to carry history | Requests are Saved in place, so a row is current state rather than a ledger. Add an append-only credentialing_events satellite written in the same call as the decision (who, when, from-status, to-status, note), the shape claims.claim_events already uses; the request row stays the fast current-state read | new migration in services/provider/migrations/ (next after 0010_add_location_to_providers.sql); write seam is the repository Update[14] |
| provider events to be durable | credentialing.* and provider.reviewed publish directly to provider.events after the DB write, best-effort, so a Kafka hiccup drops the announcement. Provider has no outbox package; adding one - event row written in the decision's transaction, drained by a relay - follows the pattern care and claims already run, and pairs naturally with the history table above | outbox table via services/provider/migrations/, relay + producer wiring in services/provider/cmd/server/main.go[22] |
| ratings to influence matching | provider_reviews already feeds a display average and count onto the profile read, computed at query time. FindNearest filters network_status = 'ACTIVE' and orders by PostGIS distance alone, so ranking on rating is a scoring change in one query, not a schema change: blend the average into the ORDER BY (or materialise it onto providers if the read cost bites) | services/provider/internal/repository/gorm_providers.go (FindNearest)[18], aggregate already available from gorm_reviews.Stats[15] |
| reviews to be a trustworthy signal | author is free text with no party link and no moderation state. A member link plus a moderation/status column makes reviews attributable and suppressible - the prerequisite for the row above, since ranking on an unmoderated average is gameable | services/provider/migrations/ for the columns; services/provider/internal/handler/reviews.go[16] for the write |
| approval to be atomic | Approve does two sequential UPDATEs in one call - the request, then the provider. Folding both into one DB transaction closes the window where a request reads APPROVED but the provider was not activated. Live data is consistent today; nothing enforces it | services/provider/internal/service/provider.go (ApproveCredentialing)[10] |
The invariant worth keeping. rating is one of the few genuinely DB-enforced vocabularies in this estate: CHECK (rating BETWEEN 1 AND 5) in the migration[2], with the handler validating the same range and returning 400 before the insert[16]. Guarded twice, so a bad rating cannot reach the average by any write path - including a direct SQL one. Credentialing status, by contrast, is a Go-enum vocabulary with no CHECK behind it (§4).
What the current population means. Every APPROVED/REJECTED note is e2e approve or e2e reject, written sub-millisecond by the e2e suite; the decision path is exercised end to end, and no admin UI drives it yet. The 11 reviews are a seeded favourable fixture across 6 providers (ratings 4 and 5 only, average 4.64), so the data holds no negative rating to test the display path against.
Known defects, with the fix:
- Three service methods have no callers.
ProviderService.SubmitCredentialing,GetCredentialingandListCredentialingexist and are OTel-instrumented, but the handler reimplements submit/get/list directly against the repository[6] and routes only approve/reject through the service[12]. Two implementations of submit means a rule added to the service one (an event, a validation) silently does not apply to the route anyone calls. Fix: point the handler at the service methods, or delete them. Where:services/provider/internal/handler/credentialing.goandservices/provider/internal/service/provider.go. reviewer_notesstores''where the DDL means NULL. The Go field is a non-pointerstring[3], so every insert without a note writes the zero value: all 23 SUBMITTED rows hold an empty string, 0 hold NULL. "No note yet" and "decided with an empty note" are then indistinguishable in SQL, and the same trap the Party and Provider pages flag. Fix: make the field*string(or normalise''to NULL on write). Where:packages/go/domain/providers.go.- REJECTED leaves the provider PENDING. Rejection touches only the request[11], so the directory cannot distinguish never-reviewed from reviewed-and-refused. Fix: set a distinct directory state on reject (detailed on the Provider page). Where:
RejectCredentialinginservices/provider/internal/service/provider.go.
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.
provider/migrations/0003_create_credentialing_requests.sql- credentialing DDL: locator UNIQUE L4, provider FK L5, status default L6, reviewer_notes nullable L90009_provider_profile.sql- provider_reviews DDL: FK L5, rating CHECK 1..5 L7, index L11packages/go/domain/providers.go#L54- CredentialingRequest model; non-pointerReviewerNotes stringL61, pointerReviewedAt *time.TimeL60packages/go/domain/providers.go#L38- ProviderReview model;Rating intL42packages/go/domain/enums.go#L62- CredentialingStatus vocabulary (no CHECK backs it)provider/internal/handler/credentialing.go#L56-submit: provider lookup,CRD-mint, SUBMITTED insert (thecredentialing_submitMCP route)credentialing.go#L158-approvehandler +credentialing.approvedemitcredentialing.go#L206-rejecthandler +credentialing.rejectedemitcredentialing.go#L183-getStatusby NPI (theprovider_credentialing_statusMCP route)provider/internal/service/provider.go#L228-ApproveCredentialing: SUBMITTED guard L237-241, provider activation L250-261 (separate UPDATE)provider.go#L265-RejectCredentialing: SUBMITTED guard L274-278, request only (provider untouched)provider.go#L178- theSubmitCredentialing/GetCredentialing/ListCredentialingservice methods, which have no callersprovider/internal/repository/gorm_credentialing.go#L60-NextLocator: counter-table UPDATE +CRD-%d-%06dgorm_credentialing.go#L56-Update: GORMSavein place, no historyprovider/internal/repository/gorm_reviews.go#L36-Stats:COALESCE(AVG(rating),0), COUNT at readprovider/internal/handler/reviews.go#L78-addReview: rating 1..5 validation L94-96 +provider.reviewedemitprovider/internal/handler/providers.go#L153-get: attachesratingavg +reviewCountto the profile, best-effortprovider/internal/repository/gorm_providers.go#L98-FindNearest: ACTIVE + distance KNN, no ratingprovider/internal/handler/handler.go#L99-mountCredentialingRoutes: submit/list/get/approve/reject/statushandler.go#L86-mountProviderRoutes: reviews list/add at L93-940008_create_seq_tables.sql- the one-rowcredentialing_locator_seqcounter tableprovider/cmd/server/main.go#L67- event producer wired toprovider.events, no outbox; emits are best-effortprovider/internal/kafka/producer.go#L44-credentialing.*+provider.reviewedevent constants (#1644)apps/web-admin/src/app/admin/App.tsx#L140- thecredentialingweb-admin resource (no reviews resource)
Live-schema facts (constraint list including the rating CHECK and the Debezium publication, credentialing status distribution, the reviewer_notes''-vs-NULL split, the counter value, the review rating distribution and average, the worked rows) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d provider · \d provider.credentialing_requests, \d provider.provider_reviews, select status, count(*) …, select rating, count(*) …, 2026-08-21.