Provider
Schema deep-dive · living document · #10 in the reading sequence
| Tables | provider.providers, provider.credentialing_requests, provider.provider_reviews[1][2][3] |
| Owner service | provider (sole writer) |
| Locators | PRV-YYYY-NNNNNN (providers) · CRD-YYYY-NNNNNN (credentialing; reviews have no locator) |
| Last updated | 2026-08-18 |
| Companion | ERD story, slide 10 · Providers (narrative) · previous: Invoices, payments & ledger |
1. Scope and usage
A Provider is a healthcare organisation or clinician the platform can book against and (eventually) settle with: GPs, physios, mental-health practitioners, hospitals, labs. The table is a directory plus a network gate: identity and contact on one axis, network_status on the other.
The network gate is the insurance-industry mechanic, not a CRUD nicety: "insurers have developed hospital networks, i.e. a list of hospitals with which the insurer has contracts" - a curated list is the insurer's cost and quality lever. Credentialing (credentialing_requests) is the doorway onto that list; provider_reviews is the member-facing quality signal on the profile.
Use this schema when you need who can deliver care and whether they are in the network. Do not look here for when they can deliver it - availability, slots and appointments deliberately live in the care service (D-12, #1015); this service has no slot table and no calendar concept.
2. Boundaries and relationships
| A Provider is not… | That concern lives in | Join |
|---|---|---|
| a Party | policy_admin.parties admits type = PROVIDER and this table carries a party_id back-ref[1] - both ends of the bridge are in place and reserved; no live row carries a link yet (see §3 party_id) | party_id / party_locator (reserved) |
| bookable time | care.provider_slots + care.appointments reference providers by provider_locator, no FK (cross-service). Care, not provider, owns slot inventory - D-12 · #1015[13] | locator |
| a claims payee | the claims-side surface today is a provider_id uuid filter on claims.prior_authorizations[14]; settlement-side coupling activates when adjudication consults the network gate (§9) | uuid soft ref (prior auth only) |
| a booking validator's cache | care re-fetches the provider at booking time over /internal/providers/{locator} and refuses non-ACTIVE[10] | HTTP, per booking |
| a login | Keycloak holds the provider portal client (web-provider); linking a session to a directory row activates when the portal lands, keyed on party_locator (§9) | none |
3. Structure
providers
| Field | Type | Req | Notes |
|---|---|---|---|
id / locator | uuid / text | ✓ | PK / UNIQUE PRV-YYYY-NNNNNN |
npi | text | ✓ | UNIQUE (twice over - see §4). US-style clinical identifier, a stand-in on a UK platform |
party_id, party_locator | uuid, text | Provider-as-party link, reserved; live rows carry Go zero values (zero-uuid / '' - §9) | |
name, specialty | text | ✓/- | Display name; free-text specialty (GP, Physiotherapy, MentalHealth, …) |
network_status | text | ✓ | Default 'PENDING'; the load-bearing column. Indexed |
address, phone, email | jsonb/text | Contact | |
photo_url | text | Profile photo (clinician profile, #1108) | |
lat, lng, location | double / geography(Point,4326) | Geo write-side + trigger-derived PostGIS point, GiST-indexed[5] | |
activated_at | timestamptz | Stamped when the provider goes ACTIVE | |
created_at, updated_at | timestamptz | ✓ | Bookkeeping |
credentialing_requests
| Field | Type | Req | Notes |
|---|---|---|---|
id / locator | uuid / text | ✓ | PK / UNIQUE CRD-YYYY-NNNNNN |
provider_id | uuid | ✓ | Real FK to providers (in-service) |
status | text | ✓ | Default 'SUBMITTED'; SUBMITTED | APPROVED | REJECTED (Go enum, no CHECK) |
submitted_at, reviewed_at | timestamptz | ✓/- | Submission always stamped; review stamped on decision |
reviewer_notes | text | Free-text decision rationale |
provider_reviews
| Field | Type | Req | Notes |
|---|---|---|---|
id | uuid | ✓ | PK. No locator - reviews are not externally addressable |
provider_id | uuid | ✓ | Real FK to providers |
author | text | ✓ | Free-text display name; no party link |
rating | int | ✓ | DB CHECK 1..5[3] |
comment | text | Free text |
Field-by-field: what and why
locator - PRV-2026-000142. Minted from a counter table, not a Postgres sequence: public.provider_locator_seq is a one-row, one-column table (val integer)[6] and the repository increments it with UPDATE provider_locator_seq SET val = val + 1 RETURNING val[7] (credentialing_locator_seq works the same way for CRD-[8]). This is the odd one out in the estate: every other service mints from a real SEQUENCE via the shared per-prefix generator (ADR-1164-05, #1169 - see Party). The row-lock on UPDATE serialises minting correctly, but the table has no PK and relies on holding exactly one row; live values (val 142 and 61) agree with the highest minted locators. Why not migrated? Predates the ADR and works; converging on the shared generator is cheap hardening, not a bug fix.
npi - the clinical identifier, unique. NPI is the US National Provider Identifier; this is a UK platform, and the narrative page is explicit that the real registers are GMC (doctors), GPhC (pharmacists), ODS (organisations) and CQC (regulated activity). Treat npi as the slot where the UK identifier scheme will land. Live values are synthetic fixtures (1000000001, 9876543210).
party_id / party_locator - the provider-as-party bridge, built at both ends. The Party page notes the PROVIDER party type is in the CHECK and reserved; this is the other end of it. The write path is finished: pass partyLocator on create and the handler validates it against policy-admin and copies both fields over[9], so realising provider-as-party is a caller change plus a backfill rather than a migration. One thing to fix on the way: the Go model declares PartyID uuid.UUID (non-pointer) and PartyLocator string[4], so an insert with no party writes the Go zero values rather than NULL - live, 92 of 95 rows carry party_id = 00000000-0000-0000-0000-000000000000 and party_locator = '' (the other 3, seeded outside Go, are NULL). See §9.
network_status - PENDING | ACTIVE | INACTIVE (Go enum[11], no CHECK; live distinct values match: 45 ACTIVE, 42 PENDING, 8 INACTIVE). The load-bearing column - but be precise about where it bears load today:
| Consumer | What ACTIVE gates | Evidence |
|---|---|---|
| care booking | ValidateActive refuses to book a non-ACTIVE provider[10] | every non-Kry booking |
| care slot search | directory sourced via /internal/providers?specialty=…&networkStatus=ACTIVE[12] | who a member can see |
| spatial search | FindNearest filters network_status = 'ACTIVE'[7] | who "near me" returns |
| claim adjudication | the target model on the narrative page has network status gating adjudication; at the pinned commit the claims service holds no reference to network_status, so this switches on when claims read the directory (§9) | activates when claims consult provider |
So today network_status is enforced at the front door (booking): an out-of-network provider cannot be booked. The back door (settlement) reads the same column the day adjudication asks for it - the gate is one directory call away, not a schema change (§9).
lat / lng / location - same pattern as Party: write lat/lng, a BEFORE INSERT OR UPDATE trigger (sync_provider_location) derives the PostGIS point, GiST index on top[5], and GET /providers/nearest runs a KNN (ORDER BY location <-> point) query[7]. The machinery is complete - trigger, GiST index, KNN endpoint, optional specialty filter - and switches on the moment coordinates land on rows: no backfill of live rows has run, so nearest-search returns an empty list today. A catchment radius is one ST_DWithin predicate on the same query (§9).
photo_url + reviews - the clinician profile (#1108). Reviews are append-only rows; the profile read aggregates AVG(rating) + count on the fly[15] - no denormalised rating column to drift. author is a free-text display string with no party linkage and no moderation state: whoever calls POST /providers/{locator}/reviews names themselves.
4. Invariants
| Invariant | Enforced by |
|---|---|
locator unique; npi unique | DB unique constraints - npi twice (a UNIQUE column constraint and a redundant unique index from migration 0004; live \d shows both providers_npi_key and idx_providers_npi) |
rating between 1 and 5 | DB CHECK[3] |
| Credentialing request / review belongs to a real provider | DB FKs (in-service)[2][3] |
location consistent with lat/lng | DB trigger[5] |
| Credentialing decision only legal from SUBMITTED | Application: approve/reject both refuse any other status[16] |
APPROVED ⇒ provider ACTIVE + activated_at stamped | Application, same code path, not a transaction: two sequential updates[16] (live data is consistent: all 19 providers with an APPROVED request are ACTIVE) |
network_status / credentialing status vocabularies | Go enums only[11] - no CHECK on either |
party_id points at a real party | Nothing in the DB - the link is validated in the create handler when a caller supplies one[9]; live rows hold Go zero values (§9) |
| Row changes captured to CDC | Debezium publication dbz_provider (live \d, all three tables) |
5. Lifecycle
Credentialing is a synchronous, in-process service flow - approve is one HTTP handler doing two row updates[16]. This contradicts architecture/services, which still describes "Temporal workflows for credentialing"; the code has no Temporal import anywhere in the service, and the narrative Providers page states the synchronous reality. Trust the code and the narrative page.
Live statuses match the diagram exactly: 23 SUBMITTED, 19 APPROVED, 19 REJECTED, nothing else.
Two lifecycle facts worth internalising:
- Rejection does not touch the provider.
RejectCredentialingupdates only the request[17]; a rejected provider staysPENDINGin the directory rather than becomingINACTIVE. Filtering consumers never see them (they filter on ACTIVE), but "PENDING" conflates "never reviewed" with "reviewed and refused". - Activation has a bypass.
PATCH /providers/{locator}/activatesets ACTIVE directly with no credentialing request at all[18] - admin convenience today; when credentialing becomes a compliance requirement, gate that handler behind an APPROVED request or a reviewer role (§9).
6. Populated example
95 providers live: a seeded GP/physio/mental-health directory plus e2e residue. Provider names are business data, not member PII; rows below are real (contact values are the seeded .test fixtures they are).
The provider - PRV-2026-000033
{
"locator": "PRV-2026-000033",
"npi": "1000000001",
"name": "Dr. Sarah Chen",
"specialty": "GP",
"network_status": "ACTIVE",
"party_id": "00000000-0000-0000-0000-000000000000",
"party_locator": "",
"address": { "city": "London", "line1": "1 Harley St", "postcode": "W1G 9QD" },
"phone": "+442071234567",
"email": "sarah.chen@ollycare.test",
"photo_url": "https://ui-avatars.com/api/?size=256&…&name=Dr.+Sarah+Chen",
"lat": null, "lng": null, "location": null,
"activated_at": "2026-07-04T13:31:00Z",
"created_at": "2026-07-04T13:30:34Z"
}| Key | Read by | What actually happens |
|---|---|---|
network_status: "ACTIVE" | care booking gate[10] + slot-search directory[12] | this provider is bookable; members see her in GP slot search |
specialty: "GP" | care's providerTypeToSpecialty map | the API enum GP resolves to exactly this string - the two services agree by convention |
npi: "1000000001" | internal lookup fallback[19] | resolvable by NPI for EDI-style callers; locator tried first |
party_id: 0000…0000 | nothing yet | the reserved party link, unset - a Go zero value rather than NULL (§9) |
lat/lng/location: null | FindNearest filter | not returned by spatial search until coordinates are written to the row |
activated_at | display | stamped by the credentialing approval that made her ACTIVE |
The credentialing decision - CRD-2026-000060
{
"locator": "CRD-2026-000060",
"provider_locator": "PRV-2026-000141",
"status": "APPROVED",
"submitted_at": "2026-08-12",
"reviewed_at": "2026-08-12",
"reviewer_notes": "e2e approve"
}The reviewer_notes value is honest provenance: the e2e suite exercises the approve path against the live service. Its sibling CRD-2026-000061 (REJECTED, "e2e reject") left its provider PENDING - the §5 rejection semantics, observable in data.
A review
{
"provider_id": "…uuid of PRV-2026-000033…",
"author": "Priya N.",
"rating": 5,
"comment": "Listened properly and explained everything.",
"created_at": "2026-07-06"
}11 reviews live, all seeded onto the profile providers; the profile read returns them with AVG(rating) computed on the fly[15].
7. Who references a Provider
| Where | Column / mechanism | Nature |
|---|---|---|
care.appointments | provider_locator | soft ref; validated ACTIVE at booking via HTTP[10] |
care.provider_slots, care.prescriptions | provider_locator | soft refs (care-owned inventory + issuance) |
| care slot search | /internal/providers?specialty=…&networkStatus=ACTIVE[12] | the bookable directory, fetched live |
claims.prior_authorizations | provider_id (uuid) | filter param only[14]; the sole claims-side touchpoint |
| web-admin | providers / credentialing resources | full CRUD over the public routes[20] |
No table in any other database holds a PRV- foreign key - the cross-service rule, as everywhere.
8. Design determinations
- Care owns slots; provider owns identity and network membership - this service has no availability concept at all, on purpose. D-12 · #1015.
- Credentialing is synchronous and in-process - no Temporal saga; the services-page claim to the contrary is stale. (§5)
- One network gate, read by whoever needs it -
network_statusis the single column booking, slot search and spatial search all filter on; adjudication reads the same column when claims start asking. (§3) - Provider-as-party is modelled ahead of need -
party_id/party_locatorand the Party CHECK'sPROVIDERtype are both in place, and the create handler already validates and copies them, so realising it is a caller change plus a backfill. (§3) npiis the reserved slot for the UK registers - GMC/GPhC/ODS/CQC are the target scheme, stated on the narrative page; a unique text column takes whichever one lands. (§9)- Reviews aggregate at read time - no denormalised rating to drift. #1108. (§3)
- The directory is category-agnostic -
specialtyis free text andnetwork_statusis the only gate, so GPs, physios, mental-health practitioners, clinics, labs and partner-sourced clinicians are all the same row shape. Adding a category is a string plus a mapping entry, not a table. (§9)
9. Caveats and extensibility
Group and individual. Nothing here is scheme- or policy-aware: the directory and network gate serve group members and (future) individual policyholders identically. The group/individual fork never reaches this schema.
What this model already absorbs. A provider row is deliberately thin: identity, contact, a free-text specialty, a geo point and one gate column. That shape is what lets one table serve GP, physio, mental-health, clinic, hospital and lab entries without a subtype table, and it is why a partner-sourced clinician (a Kry-style third-party directory) can join the same directory rather than needing a parallel one. The satellites are the same story: credentialing carries the vetting decision, reviews carry the member-facing quality signal, both hanging off the provider by a real in-service FK - see Provider credentialing & reviews (#18) for their own deep-dive.
Extension points:
| When we need … | What to add | Where |
|---|---|---|
| a new provider category (clinic, hospital, lab, pharmacy, dentist) | a specialty string on the row plus the API-enum entry that maps to it; no schema change | provider.providers.specialty[1] · care's providerTypeToSpecialty map, read through /internal/providers?specialty=…[12] |
| "near me" search live | coordinates on provider rows; the trigger derives the PostGIS point and the GiST index and KNN endpoint are already in place | backfill lat/lng on provider.providers; sync_provider_location[5] · FindNearest[7] |
| a catchment radius or "within N km" filter | one ST_DWithin predicate beside the existing ACTIVE and specialty filters | FindNearest[7] and the /providers/nearest query params |
| UK registers (GMC, GPhC, ODS, CQC) | sibling identifier columns beside npi, plus the lookup fallback that resolves them | migration under services/provider/migrations/ · internal get, locator-first with identifier fallback[19] |
| providers as parties (a provider portal login, provider-side payments) | mint PROVIDER parties and pass partyLocator on create; validation and copy already exist, then backfill existing rows | create handler[9] · Party CHECK on Party |
| network status to gate claim settlement | claims reading the directory (or a projection of it) at adjudication; the column and the read endpoint both exist | /internal/providers/{locator}[19] → the claims side[14] |
| partner-sourced providers in the same directory | rows minted from the partner feed with their own specialty and network_status; care already books partner slots, and the ACTIVE gate applies to directory rows as it stands | POST /providers[20] · booking gate[10] |
| credentialing to become a governed process (documents, expiry, re-credentialing, reviewer roles) | columns or satellite tables on credentialing_requests, plus a gate on the activate bypass | Provider credentialing & reviews (#18) · ApproveCredentialing[16] |
| moderated, attributable reviews | a moderation status column and a party link beside author; the read-time aggregate needs no change | provider_reviews[3] · aggregate[15] · (#18) |
| locator minting converged on the estate standard | swap the counter tables for the shared per-prefix SEQUENCE generator | provider_locator_seq / credentialing_locator_seq[6][7]; ADR-1164-05, #1169 |
Known defects, and the fix:
party_id/party_locatorhold Go zero values, not NULL on 92 of 95 rows - non-pointer model fields[4] writing00000000-…and''. This is the''vs NULL trap the platform has been bitten by before, and it must be cleaned before the party bridge is realised. Fix:*uuid.UUID/*stringinpackages/go/domain/providers.go, plus anUPDATE … SET party_id = NULL WHERE party_id = '00000000-…'migration.npiis double-constrained - aUNIQUEcolumn constraint and a redundant unique index from migration 0004 (live\dshows bothproviders_npi_keyandidx_providers_npi). Fix: dropidx_providers_npiin a new migration.- REJECTED credentialing leaves the provider PENDING.
RejectCredentialingupdates only the request[17], so PENDING conflates "never reviewed" with "reviewed and refused". Fix: setnetwork_status = INACTIVEon reject, in that same service call. - The activate endpoint bypasses credentialing.
PATCH /providers/{locator}/activatesets ACTIVE with no request at all[18]. Fix: require an APPROVED request, or a reviewer role, inActivateProviderbefore compliance depends on it. - APPROVED ⇒ ACTIVE is two sequential updates, not one transaction[16] - a crash between them leaves an approved request on a PENDING provider (live data is consistent: all 19 are ACTIVE). Fix: wrap both in one
db.Transaction. - No DB enforcement of any vocabulary -
network_status, credentialingstatusandspecialtyare baretextheld to Go enums[11], andspecialtyadditionally has to agree with care's type-to-specialty map by convention. Fix: CHECK constraints on the two status columns; forspecialty, a shared constant inpackages/go/domain. - Review
authoris unverified free text - whoever callsPOST /providers/{locator}/reviewsnames themselves. Fix: derive the author from the caller'sparty_locatorclaim in the reviews handler.
References
Code links are pinned to commit 8329d7b on main (2026-08-19); the file is the anchor if lines drift. Pins are checked mechanically by docs/site/scripts/check-code-refs.py.
provider/migrations/0002_create_providers.sql- providers DDL: npi UNIQUE L5, party_id/party_locator L6-7, network_status default L100003_create_credentialing_requests.sql- credentialing DDL, FK L5, status default L60009_provider_profile.sql- photo_url + provider_reviews (rating CHECK L7)packages/go/domain/providers.go- the three models; non-pointerPartyID uuid.UUIDL130010_add_location_to_providers.sql- PostGIS columns,sync_provider_locationtrigger, GiST index0008_create_seq_tables.sql- the one-row counter tablesinternal/repository/gorm_providers.go#L86-NextLocatorcounter UPDATE L86-96;FindNearestKNN with ACTIVE + location filters L98-118internal/repository/gorm_credentialing.go#L60-CRD-counter mintinginternal/handler/providers.go#L100- create: party validation via policy-admin + PartyID/PartyLocator copycare/internal/client/provider.go#L49-ValidateActive: non-ACTIVE providers are unbookablepackages/go/domain/enums.go#L54- NetworkStatus + CredentialingStatus vocabulariesinternal/handler/internal.go#L16-listBySpecialty, the no-JWT directory read care's slot search usescare/migrations/00001_care_schema.sql#L18-provider_slotsliving in care, not hereclaims/internal/repository/gorm_priorauth.go#L81- the only claims-side provider reference (prior-auth filter)internal/repository/gorm_reviews.go#L36- review stats aggregated at readinternal/service/provider.go#L228-ApproveCredentialing: SUBMITTED guard L237-241, provider activation L250-261internal/service/provider.go#L265-RejectCredentialing: request only, provider untouchedinternal/service/provider.go#L142-ActivateProvider, the credentialing bypassinternal/handler/internal.go#L38- internal get: locator-first, NPI fallbackinternal/handler/handler.go#L77- the public route table (providers + credentialing + reviews)
Live-schema facts (constraint list including the double npi uniqueness, Debezium publication, network_status / credentialing status distributions, party_id zero-uuid observation, counter values, geo-column emptiness) come from PGPASSWORD=… psql -h 10.0.1.2 -U olly -d provider · \d provider.providers, \d provider.credentialing_requests, \d provider.provider_reviews, \d public.provider_locator_seq, select network_status, count(*) …, 2026-08-18.