# HMIS data quality toolkit (demonstration)

Loads an HMIS CSV export into a reporting database, runs 14 data quality
checks, tests the backup and writes a report that agencies can work from.

**Demonstration.** All records in `data/export` are synthetic, generated by
`generate_synthetic.py` with known problems planted at fixed rates. No client
data is included. The files use a subset of the HUD HMIS CSV export columns,
so the checks run unchanged against a real export.

## Run it

Python 3.9 or later, standard library only.

```
python3 generate_synthetic.py        # writes data/export/*.csv
python3 run_checks.py --period-start 2025-10-01 --period-end 2026-09-30
```

Outputs in `output/`:

| File | What it is |
| --- | --- |
| `report.html` | Summary, findings by check, error rate by project, rules, examples and administration checks |
| `flagged_records.csv` | One row per flagged record, ready to split by agency |
| `dq_summary.csv` | Flagged count, records checked and rate for each check and project |
| `run_log.json` | Row counts, file hashes, timings, query plans and the restore test result |
| `hmis_reporting.sqlite` | The reporting database |
| `backups/` | The tested backup copy |

To run against a real export, point `--export` at the folder holding
`Project.csv`, `Client.csv`, `Enrollment.csv` and `Exit.csv`, and replace
`data/holidays.csv` with the agency's holiday calendar. Keep real exports, the
database and its backups on encrypted, access-controlled storage.

## How it is built

**The HMIS stays the system of record.** The reporting database is rebuilt
from each export. Loaded export tables are read-only: a trigger blocks any
edit, so a correction has to be made in the HMIS and arrive with the next
export. Each load records its time, reporting period and the SHA-256 hash of
every source file.

**Findings are tracked, not just counted.** Every flagged record lands in
`dq_finding` with a status (`open`, `sent to agency`, `corrected in HMIS`,
`accepted as is`). A second trigger logs each status change with a timestamp in
`dq_finding_history`, so follow-up with agencies is auditable.

**Indexes follow the checks.** The overlap and duplicate checks are self-joins;
`ix_enrollment_person (PersonalID, EntryDate)`, `ix_client_ssn` (partial, valid
SSNs only) and `ix_client_dob_name` keep them on index searches. The report
prints the query plans so a change that drops an index shows up.

**Backups are tested, not assumed.** Each run copies the database with
SQLite's online backup API, runs `PRAGMA integrity_check` on the copy and
compares every table's row count and content hash with the live database.

## The checks

Rules live in `sql/checks.sql`, one block per check with its plain-language
rule, flag query and universe (denominator) query. Adding a check means adding
a block.

| Check | Area | Finds |
| --- | --- | --- |
| DQ01 | Identifiers | Name missing, unknown or refused |
| DQ02 | Identifiers | SSN missing or failing format rules |
| DQ03 | Identifiers | DOB missing, after entry, or age over 105 |
| DQ04 | Universal data elements | Veteran status missing for adults |
| DQ05 | Universal data elements | Disabling condition missing |
| DQ06 | Universal data elements | Household without exactly one head of household |
| DQ07 | Universal data elements | Prior living situation missing for heads of household and adults |
| DQ08 | Exits | Exit destination 8, 9, 99, 30 or blank |
| DQ09 | Exits | Exit date before entry date |
| DQ10 | Timeliness | Entry created more than 4 business days after entry date |
| DQ11 | Timeliness | Exit created more than 4 business days after exit date |
| DQ12 | Consistency | Overlapping emergency shelter, transitional housing or safe haven stays |
| DQ13 | Consistency | Possible duplicate clients: shared valid SSN, or same DOB, last name and first-name start |
| DQ14 | Timeliness | Shelter or outreach enrollment open over 90 days |

The timeliness threshold and the 90-day review window are set in
`sql/checks.sql`; change them to match the CoC's data quality plan.

## Planted problems and what the run found

The generator plants 12 duplicate client pairs, 10 overlapping stays and 2
exits keyed before entry. The current run flags all 12 duplicate pairs (DQ13),
all 10 overlaps (DQ12) and both exit date errors (DQ09). The other checks find
problems planted at random rates; see `output/report.html`.