FreeWIS · Atomic CCWIS — how it actually works

The report isn't written at the end. It falls out of the structure.

The lead architect at one of the big consultancies told Matt "SQL can't do compliance." This is the rebuttal, layer by layer. In FreeWIS, the federal report (AFCARS and its siblings) is the output of a join — sitting on a schema where a non-compliant record is structurally impossible to save in the first place. Here's the build, from the atoms up.

controlled data→enforced structure→a report ACF accepts

Atom · the vocabulary is enforced by the engine

Every coded answer has to be a real one

Gender, race, tribal membership, legal status — every coded value must come from one master list, and the database itself refuses to save anything that isn't on it. Not a form check an app bug or a direct-SQL session could route around — a rule the engine enforces on every write, from any caller.

-- every code column pair points straight at the master list
CONSTRAINT FK_PRSN_GENDER FOREIGN KEY ([CT_GENDER],[CD_GENDER])
  REFERENCES [dbo].[CODES]([CODE_TYPE],[CODE])

database/dbo/Tables/CODES.sql · PRSN.sql — composite-FK CODES enforcement (patent-pending)

✕ An invalid code never lands — the INSERT is rejected by the engine.

Atom · no half-written records

You can't save a record that's missing what reporting needs

If the schema requires a field, the save is refused right then — with the exact list of what's missing — instead of silently succeeding and becoming a reporting gap someone discovers months later when the report is due.

The write path computes the required-column set live from the database's own catalog on every call (never hard-coded), and rejects an incomplete write before any SQL touches the table.

// required set derived fresh from the catalog, then:
if (missing.length) return { error: "missing_required", missing }

agent/mcp-servers/razor-db/server.py — the "hard-requirements floor" (v2.3, Matt ruling 2026-07-15)

✕ An incomplete record never lands — you're told exactly what's missing.

Structure · the field-to-federal crosswalk

Every column knows which federal element it feeds

Each database column a report needs has one row that spells out exactly which report it feeds (AFCARS / NCANDS / NYTD / FFPSA), which numbered element, how to transform it, and whether it's required. That mapping isn't a spreadsheet maintained off to the side — it's a table in the same database as the data, and every edit to it is tracked over time.

database/dbo/Tables/FIELD_CROSSWALK.sql — the FIELD_CROSSWALK metadata crosswalk (patent-pending)

Structure · the schema describes itself

Ask the database what it is, and it tells you

A rebuilt-on-demand table lists every table and column, its type, its keys, and which federal element it feeds — generated by reading the database's own catalog plus the crosswalk above. Not a Word doc someone hand-maintains until it quietly goes stale. It's what an auditor or a CCWIS self-assessment reads from directly.

FR_DATA_DICTIONARY.sql · usp_Refresh_DataDictionary.sql — the self-documenting data dictionary (patent-pending)

Structure · role-context routing

The same field means the right thing for each person

A birthdate is one federal element if it's the child's, a different one if it's a parent's, a foster parent's, or an adoptive parent's. The system works out who fills which role from the case's actual relationships — so one column safely serves many report elements without copying the data per role.

-- the crosswalk join is filtered by who the person is
WHERE RoleContext = 'FOSTER1' AND ELEMENT_NUMBER BETWEEN 124 AND 135

database/dbo/StoredProcedures/usp.AFCARS_FC_Report.sql — role-context AFCARS routing (patent-pending)

The report that falls out

Start from the federal list of 185 elements — answer each one

Running AFCARS doesn't hand-assemble a report from whatever data happens to exist. It starts from the government's own official list of the 185 required elements and asks, for each one, what this case says — if anything. Every element appears, every time, in federal order, honestly marked N/A where nothing applies.

-- the federal master list drives the report; data joins in
FROM dbo.FR_AFCARS_2020 f
LEFT JOIN MappedData m ON m.ELEMENT_NUMBER = f.FieldNumber

usp.AFCARS_FC_Report.sql, final SELECT — the unified multi-report model (patent-pending). The same shape drives NCANDS / NYTD / FFPSA off their own federal master tables.

This is the file you generate for a synthetic caseload — and load into ACF's own validator to check. Now, not "could, if."

The rail that runs alongside all six — why it can be trusted, not just complete

A human attests before anything becomes a case-of-record

None of the above matters if nobody can say who put the information in and whether they meant it. An AI assistant can draft a proposed fact from a conversation — but nothing reaches the case-of-record until a caseworker attests to it, and that attestation is its own permanent, timestamped event chained to whatever it responds to. Pepe drafts, a human attests, only then does the database write.

Honest status: this liability firewall is designed and partly built. The append-only audit triggers and the cryptographic-ledger conversion are a designed target, not a live guarantee yet — the real-data gates (ADR-002 steps 2 & 6) stand before any real child's information is ever in the system. We say what's true today.