Plan: System-Wide Reference Data Management

On this page

Status

COMPLETE

Status: COMPLETE (March 2026)

All 6 steps implemented across branches feature/test-coverage, feature/admin-unit-registry, feature/worker-identity, and feature/ref-data-docs. Key commits: 633ee53, 1435251, 15de344. See GitLab Issues #7-#9, #12-#13.

Context

CRAIG currently has ~70+ unconstrained TEXT columns across 8 services that represent constrained, system-wide data — county names, worker identities, placement types, abuse types, race/ethnicity, permanency goals, etc. None of these have database CHECK constraints, lookup tables, or runtime validation beyond application-layer state machine transitions (which only cover status fields, not category fields).

This matters for three reasons:

  1. Federal reporting: AFCARS requires ~66 data elements with specific coded values (e.g., county as 3-digit FIPS code, sex as 1/2, race as individual flags). NCANDS has similar requirements. CRAIG currently stores "Fulton" not "121", "Male" not "1", "foster_care" not "1". The reporting service cannot generate valid federal flat files without a code translation layer.

  2. Cross-service consistency: county appears in 3 tables across 2 services. permanency_goal appears in both cases and placement. source_service appears in reporting and security. A typo in any of these silently creates data inconsistencies.

  3. Worker identity: Some services use claims.preferred_username (e.g., "jane.doe"), others use claims.sub (Keycloak UUID). There is no worker directory linking users to counties, offices, or org units.

What OpenStack Keystone provides that CRAIG lacks

Keystone Function CRAIG Equivalent Gap

Service catalog

Hardcoded URLs in 4 configs

No dynamic registry

Regions/Domains

jurisdiction env var

Single-value, per-deployment

Users & Groups

Keycloak (6 roles, flat)

No org structure, no county/office attributes

Projects/Tenants

None

No multi-tenancy or data scoping

Reference data catalog

Hardcoded constants in seed tool only

No runtime code tables

The "county" problem — jurisdictional flexibility

CRAIG hardcodes "county" as the organizational unit in 3 database columns (referrals.county, cases.county, foster_homes.county), case number generation ({COUNTY}-{YYYYMMDD}-{HEX}), seed data (20 Georgia county names), and web UI filters. But US child welfare systems are administered at different levels:

Jurisdiction Type Organizational Unit Examples

State-administered, county-based

County

Georgia (159 counties), Ohio (88 counties)

State-administered, region-based

Region

Texas (11 DFPS regions), Florida (20 circuits)

County-administered

County (each is independent)

California (58 counties), Pennsylvania (67 counties)

Tribal

Chapter, district, or none

Navajo Nation (110 chapters), Cherokee Nation

City-administered

Borough, district

NYC (5 boroughs)

Territory

Municipality

Puerto Rico (78 municipalities)

Current codebase signals: investigations.assignment_area and rate_tables.jurisdiction already use generic names rather than "county" — proving the pattern works. The generalization is a rename of 3 columns + case number format + seed data, not a schema redesign.

Do we need multi-tenancy?

No — not in the OpenStack sense. CRAIG’s deployment model is correct: one instance per jurisdiction (a state, tribe, territory, or city that administers its own child welfare system). This matches how CCWIS systems are procured and deployed in practice — Georgia DFCS runs their own instance, Texas DFPS runs theirs, Navajo Nation runs theirs.

What we DO need:

  1. Generalize "county" to "administrative_unit" — a configurable concept that could be county, region, borough, chapter, district, or municipality depending on the deployment

  2. Make the hierarchy deployment-configurable — the craig-reference crate provides ALL FIPS codes for ALL states; the admin registry on craig-security defines which administrative units are "in scope" for THIS deployment

  3. Support jurisdiction-specific labels — the Web UI should display "County" in Georgia, "Region" in Texas, "Chapter" on Navajo Nation. This is a single config value, not multi-tenancy.

What we do NOT need:

  • Multiple jurisdictions sharing one database or one service instance

  • Tenant isolation, tenant-scoped tokens, or data partitioning

  • A formal multi-tenancy framework

Recommendation: Shared Crate + Admin Service (Hybrid Approach)

Why not just a service?

A Keystone-style service that owns ALL reference data would be overkill and dangerous:

  • Single point of failure — every API call would need to validate against it

  • Latency — network round-trip for every coded value lookup

  • Complexity — most reference data is truly immutable (FIPS codes don’t change, AFCARS element definitions update every few years)

  • Tight coupling — every service hard-depends on it at runtime

Why not just a crate?

A compile-time crate cannot handle data that varies by deployment:

  • Which administrative units are "in scope" for this jurisdiction

  • Which workers are assigned to which offices

  • Deployment-specific configuration (court districts, service areas, etc.)

The hybrid: craig-reference crate + admin endpoints on craig-security

Tier 1 — craig-reference shared crate (compile-time, zero overhead):

  • FIPS state codes (50 states + territories)

  • AFCARS data element definitions + coded values

  • NCANDS data element definitions + coded values

  • Federal code enums: Gender, Race, Ethnicity, PlacementType, PermanencyGoal, RemovalManner, DischargeReason, MaltreatmentType, ReporterType, ContactType, CourtOrderType, LicenseType, Disposition, AbuseType, CasePlanTaskResponsible, HouseholdRole

  • Code translation functions: county_name_to_fips(), placement_type_to_afcars(), etc.

  • Validation functions usable by any service at the API boundary

Tier 2 — Admin endpoints on craig-security (runtime, for deployment-specific data):

  • Jurisdiction configuration (expand existing single-value to a managed record with label customization)

  • Administrative unit registry (replaces hardcoded "county" — configurable per deployment as county/region/borough/chapter/district/municipality, with FIPS codes where applicable)

  • Administrative unit label config: e.g., CRAIG_SECURITY__ADMIN_UNIT_LABEL=County (Georgia) or Region (Texas) or Chapter (Navajo Nation) — drives UI labels

  • Service catalog (optional — current Docker DNS approach works fine for MVP)

  • No separate worker directory — standardize on claims.sub everywhere and let Keycloak manage org attributes via custom user attributes (admin_unit, office, district)

Why craig-security and not a new service?

Phase 8 already designates craig-security as the "admin/compliance" service. It handles audit logs, security reviews, archive records, and NIST controls. Adding jurisdiction configuration and administrative unit registry aligns with its role as the system administration service. Avoids adding yet another service, database, Docker target, CI job, etc.

Pros and Cons Summary

Pros

  • Type-safe at compile time: Rust enums prevent invalid coded values; match is exhaustive

  • Zero runtime overhead for immutable federal codes (FIPS, AFCARS, NCANDS)

  • Enables federal reporting: craig-reporting can generate valid AFCARS/NCANDS flat files by calling craig-reference translation functions

  • Cross-service consistency: All services share the same enum definitions

  • Incremental: Can adopt crate-level validation per-service without big-bang migration

  • Minimal new infrastructure: No new service, no new database, no new Docker target

  • DB CHECK constraints: Migration to add constraints using crate-defined allowed values

  • Fixes worker identity bug: Standardize on claims.sub with the crate defining the convention

Cons / Risks

  • Redeployment for federal code changes: If AFCARS adds a new coded value, all services need recompilation. Mitigated by: federal standards change every 3-5 years, and CRAIG already requires redeploy for any code change.

  • Migration effort: Existing free-text data needs to be mapped to new coded values. Requires a data migration for each service database.

  • API breaking changes: Request/response schemas change from String to enum-backed types. Mitigated by: keeping the wire format as strings but validating against the crate’s enums server-side.

  • Scope creep: Administrative unit registry adds complexity to craig-security. Mitigated by: keeping it to 4-5 simple CRUD endpoints.

What this does NOT include (intentionally)

  • Service catalog/registry: Docker Compose DNS is sufficient. A formal catalog adds complexity without solving a real problem at CRAIG’s scale.

  • Worker directory: Keycloak custom attributes (admin_unit, office, supervisor) are the right solution, not a separate service. Can be added to Keycloak realm config without code changes.

  • Multi-tenancy: CRAIG is one deployment per jurisdiction (state, tribe, territory, or city). This work generalizes the organizational unit concept so any jurisdiction type can use CRAIG without code changes — but it does NOT add tenant isolation, shared-database partitioning, or cross-jurisdiction data sharing.

  • Cross-service UUID validation: Services trusting UUIDs from events is the correct microservice pattern. Adding cross-service validation creates tight coupling and defeats the purpose of event-driven architecture.

Implementation Plan

Step 1: craig-reference Shared Crate

New crate: crates/craig-reference/

Modules:

  • fips.rs — State enum (50 states + DC + territories, with FIPS 2-digit code and USPS abbreviation), AdminUnit struct (name + FIPS code + state + unit_type), complete FIPS county data for all states (3,243 counties), AdminUnitType enum (County, Region, Borough, Chapter, District, Municipality, Parish, Other)

  • afcars.rs — All AFCARS coded value enums with to_code() → u8 and from_code(u8) methods

  • ncands.rs — NCANDS coded value enums

  • enums.rs — Shared domain enums used across services: Gender, Race (flags, not exclusive), Ethnicity, PlacementType, PermanencyGoal, RemovalManner, DischargeReason, MaltreatmentType, ReporterType, Disposition, ContactType, CourtOrderType, LicenseType, TrainingType, HouseholdRole, PaymentType, AbuseType

  • validation.rs — validate_admin_unit(name: &str, state: &State) → Result<&AdminUnit>, validate_state(code: &str) → Result<&State>, etc.

  • translate.rs — admin_unit_to_fips(name: &str, state: &State) → Option<String> (3-digit FIPS), gender_to_afcars(g: &Gender) → u8, etc.

Each enum derives Serialize, Deserialize, Display, FromStr, ToSchema (utoipa) — enabling direct use in API request/response types and OpenAPI docs.

Workspace changes:

  • Add "crates/craig-reference" to workspace members in root Cargo.toml

  • Add craig-reference to workspace dependencies

Tests: Unit tests for all enums (roundtrip serialization, FIPS lookups, AFCARS code translation)

Step 2: Adopt craig-reference in Services (Incremental)

For each service, in priority order:

craig-cases (highest priority — most free-text fields, feeds AFCARS/NCANDS):

  • Add craig-reference dependency

  • Rename county → admin_unit in referrals and cases tables (migration: ALTER TABLE referrals RENAME COLUMN county TO admin_unit; ALTER TABLE cases RENAME COLUMN county TO admin_unit;)

  • Update case number generation from {COUNTY}-{YYYYMMDD}-{HEX} to {ADMIN_UNIT}-{YYYYMMDD}-{HEX}

  • Validate admin_unit, reporter_type, abuse_type, gender, race, ethnicity, permanency_goal, contact_type, court_order_type, household_role, disposition at API boundary using crate enums

  • Keep DB columns as TEXT (enum .to_string() for storage, parse on read)

  • Add CHECK constraints in a new migration for each validated column

  • Standardize all created_by/recorded_by/assessed_by on claims.sub

craig-placement (high priority — feeds AFCARS):

  • Rename county → admin_unit in foster_homes table

  • Validate admin_unit, license_type, placement_type, end_reason, removal_reason, permanency_goal, training_type, relationship

craig-financial (medium priority):

  • Validate jurisdiction, payment_type

craig-exchange (medium priority):

  • Validate sending_state, receiving_state using State enum

  • Validate partner_type, direction, icpc_request_type

craig-reporting (high priority — consumes coded values for AFCARS/NCANDS):

  • Add craig-reference dependency

  • Use translation functions in AFCARS/NCANDS flat file generation

  • Validate source_service, severity, issue_type, metric_type

craig-security (low priority — internal admin data):

  • Validate review_type, service, action, resource_type

  • NIST control_family and control_id validation

craig-rules (no changes needed — rule sets are user-defined, not coded)

Step 3: Administrative Unit Registry on craig-security

New endpoints (4):

Method Path Handler Role

GET

/v1/security/admin/units

list_admin_units

admin

POST

/v1/security/admin/units

create_admin_unit

admin

PUT

/v1/security/admin/units/{id}

update_admin_unit

admin

DELETE

/v1/security/admin/units/{id}

delete_admin_unit

admin

New migration — admin_unit_registry table:

CREATE TABLE admin_unit_registry (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name         TEXT NOT NULL,           -- "Fulton" / "Region 6" / "Chinle Chapter"
    unit_type    TEXT NOT NULL,           -- county, region, borough, chapter, district, municipality
    fips_code    TEXT,                    -- 3-digit FIPS where applicable (NULL for tribal/custom)
    state_fips   TEXT NOT NULL,           -- 2-digit FIPS of parent state/territory
    jurisdiction TEXT NOT NULL,           -- "georgia" / "texas" / "navajo-nation"
    active       BOOLEAN NOT NULL DEFAULT true,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(name, jurisdiction)
);

Jurisdiction config endpoint (1):

Method Path Handler Role

GET

/v1/security/admin/config

get_deployment_config

any authenticated

Returns deployment-level config including admin_unit_label (e.g., "County", "Region", "Chapter") — used by Web UI for labels, form placeholders, column headers.

Seed data: Populate with Georgia counties (all 159, with FIPS codes from craig-reference crate).

Validation flow: Services validate admin_unit against the crate’s static FIPS data (is this a real place?) and optionally against the registry (is this place in scope for this deployment?).

Step 4: Standardize Worker Identity

  • Document convention: ALL services use claims.sub (Keycloak UUID) for *_by fields

  • Fix inconsistent services (craig-placement, craig-exchange already use claims.sub; craig-cases, craig-financial, craig-reporting, craig-security use preferred_username)

  • Migration: Add *_by_sub columns where needed, backfill where possible, deprecate preferred_username storage

  • Add Keycloak custom attributes: admin_unit, office, district to user profiles (realm config change only, no code needed)

Step 5: Update Seed Tool, CLI, Web, Tests

  • craig-seed: Replace hardcoded GEORGIA_COUNTIES and other const arrays with craig-reference enum iterators; rename county fields to admin_unit

  • craig-cli: Use enums for tab completion and input validation; rename --county flags to --admin-unit

  • craig-web: Use enums for dropdown options in forms; use deployment config for admin unit label ("County", "Region", etc.) in column headers and form labels

  • craig-test-lib: Use enums in builders; rename county references

  • E2E tests: Update any hardcoded county values to admin_unit

Step 6: Documentation & Roadmap

  • Update architecture.adoc with craig-reference crate description

  • Update developer-guide.adoc with crate in workspace tree

  • Update implementation-guide.adoc — add reference data section, note county→admin_unit generalization

  • Update roadmap.adoc — add reference data items to appropriate phase

  • Document AFCARS/NCANDS code mappings

  • Document jurisdictional flexibility (how to deploy for a state vs tribe vs territory)

  • Update MEMORY.md

Verification

  1. cargo test -p craig-reference — all enum roundtrips, FIPS lookups, translations pass

  2. cargo clippy --workspace --locked — zero warnings

  3. Send invalid admin_unit to craig-cases API → 400 with descriptive error

  4. Send valid coded value → stored correctly, queryable

  5. AFCARS translation: admin_unit_to_fips("Fulton", State::Georgia) → "121"

  6. Texas deployment test: admin_unit_to_fips("Region 6", State::Texas) works with custom unit_type

  7. cargo xtask dev restart → all services healthy

  8. E2E tests pass with updated coded values

  9. Pre-push hook passes

Scope / Phasing

This is a cross-cutting concern that touches many services. Recommended phasing:

  1. Immediate (new crate): Steps 1 + 6 — create craig-reference crate with all enums and FIPS data. No services change yet.

  2. Per-service adoption: Step 2 — adopt incrementally, one service at a time. Each service is an independent commit.

  3. Admin unit registry: Step 3 — add to craig-security after crate exists.

  4. Worker identity: Step 4 — standardize across services.

  5. Tooling: Step 5 — update seed, CLI, web.

Edit this page · latest