Data Model: Reporting & Data Quality

On this page

Overview

The reporting service provides automated data quality monitoring and federal report generation. It consumes events from other CRAIG services (cases, placement, financial) to detect data quality issues, and generates AFCARS (quarterly) and NCANDS (annual) submissions for federal reporting to the Administration for Children and Families (ACF).

Data quality issues are created automatically when inbound events fail validation checks (missing required fields, invalid values). AFCARS and NCANDS submissions follow an approval workflow: draft → validated → reviewed → approved → transmitted. For NCANDS, generate finalizes to validated only when the file has reportable records and zero validation errors; a file with genuine defects (e.g. >4 maltreatment types) or no reportable records finalizes to validated_with_errors, a terminal state the workflow cannot advance and transmit refuses (#782). The Child File carries only children whose report received a disposition in the FFY — still-pending reports are excluded as not-yet-reportable (ADR-040 capture-complete amendment), reported in the year they are disposed.

The fleet-standard platform tables also live in this schema and are documented centrally: event_outbox / event_inbox / event_inbox_collisions (ADR-022 and the ADR-062 §A attempt machine), request_claims (ADR-062 §B), and schema_compat_floor (ADR-063).

Tables

Table Purpose

data_quality_issues

Tracks data quality problems detected across all CRAIG services (missing fields, invalid values, cross-service inconsistencies)

afcars_submissions

AFCARS quarterly submission records with review/approval audit trail

ncands_submissions

NCANDS annual submission records with review/approval audit trail

ncands_child_rows

Per-(report, child) materialized NCANDS Child File rows for a submission (the federal #1–34 codes); written when a submission is generated, read by the export emitter (FK → ncands_submissions ON DELETE CASCADE)

data_quality_metrics

Point-in-time metrics for dashboards (field completeness, AFCARS/NCANDS readiness scores)

upload_attempts

ADR-062 §U upload-attempt tombstones (SU2 #1248): one immutable row per client upload/export operation, id = the client-held request id; generation-fenced CAS state machine + replay decision table live in craig-store::attempts (shared crates); kinds here: afcars_export / ncands_export (replace kinds — the submissions carry export_sha256 + current_export_attempt_id + export_generation). 31 d terminal retention (reconciler-pruned); no FK — target_id is polymorphic by design.

ER Diagram

data_quality_issuesuuididPKtextsource_serviceuuidsource_record_idtextissue_typetextfield_namenullabletextdescriptiontextseveritybooleanresolvedtextresolved_bynullabletimestamptzresolved_atnullabletimestamptzcreated_atafcars_submissionsuuididPKtextreporting_periodtextstatusintegerrecord_countintegervalidation_errorsvarcharexport_object_keynullable, craig-store pathtimestamptzexport_generated_atnullablecharexport_sha256nullable, ADR-062 §U artifact digest (SU2)uuidcurrent_export_attempt_idnullable, §U replace pointer (SU2)bigintexport_generation§U source-drift fence (SU2)textreviewed_bynullabletimestamptzreviewed_atnullabletextapproved_bynullabletimestamptzapproved_atnullabletimestamptztransmitted_atnullabletimestamptzcreated_attimestamptzupdated_atncands_submissionsuuididPKintegerreporting_yeartextstatusintegerrecord_countintegervalidation_errorsintegerchild_fatalitiesvarcharexport_object_keynullable, craig-store pathtimestamptzexport_generated_atnullablecharexport_sha256nullable, ADR-062 §U artifact digest (SU2)uuidcurrent_export_attempt_idnullable, §U replace pointer (SU2)bigintexport_generation§U source-drift fence (SU2)textreviewed_bynullabletimestamptzreviewed_atnullabletextapproved_bynullabletimestamptzapproved_atnullabletimestamptztransmitted_atnullabletimestamptzcreated_attimestamptzupdated_atncands_child_rowsuuididPKuuidsubmission_idFKuuidsource_report_idnullable, provenance (not emitted)uuidsource_victim_person_idprovenance (not emitted)integersubmission_year#1textstate#2 FIPS state codetextreport_id_fieldnullable, #3 surrogatetextchild_id_fieldnullable, #4 surrogatetextstate_county_fipsnullable, #5datereport_datenullable, #6dateinvestigation_startnullable, #8smallintreport_source_codenullable, #10smallintreport_disposition_codenullable, #11datedisposition_datenullable, #12smallintchild_age_codenullable, #14smallintchild_sex_codenullable, #15smallintrace_aiannullable, #16smallintrace_asiannullable, #17smallintrace_blacknullable, #18smallintrace_nhopinullable, #19smallintrace_whitenullable, #20smallintrace_unable_determinednullable, #21smallintchild_ethnicity_codenullable, #22smallintmal1_typenullable, #26smallintmal1_levnullable, #27smallintmal2_typenullable, #28smallintmal2_levnullable, #29smallintmal3_typenullable, #30smallintmal3_levnullable, #31smallintmal4_typenullable, #32smallintmal4_levnullable, #33smallintmaltreatment_deathnullable, #34booleanchild_fatalitybooleanrow_validtextvalidation_notesnullabletimestamptzcreated_atdata_quality_metricsuuididPKtextmetric_typenumericmetric_valuejsonbmetadatanullabletimestamptzmeasured_atmaterializes (one row per (report, child))

Indexes

Table Index Columns

data_quality_issues

idx_dqi_source

source_service, source_record_id

data_quality_issues

idx_dqi_severity

severity, resolved

afcars_submissions

idx_afcars_period

reporting_period

afcars_submissions

idx_afcars_status

status

ncands_submissions

idx_ncands_year

reporting_year

ncands_submissions

idx_ncands_status

status

ncands_child_rows

idx_ncands_child_rows_submission

submission_id

Cross-Service References

The reporting service does not have direct foreign keys to other databases. Instead, it stores source_record_id (UUID) references that correspond to records in other services:

  • source_service = "craig-cases" → cases.id in craig_cases database

  • source_service = "craig-placement" → placements.id in craig_placement database

  • source_service = "craig-financial" → payments.id in craig_financial database

For NCANDS generation, the service makes HTTP calls to collect data from upstream services using the caller’s JWT token. AFCARS generation does not yet collect upstream data: generate_afcars creates an honest zero-record draft stub that cannot advance the federal workflow or be transmitted, pending the real extraction tracked in #949 (#762).

Edit this page · latest