deped-equipment 0.1.1

Equipment cleaner extracted from the DepEd asset pipeline.

deped-equipment

deped-equipment cleans equipment CSV exports into a standalone SQLite database. It consumes three upstream artifacts: lookups.db from deped-template, entities.db from deped-entity, and personnel.db from deped-hr.

What This Package Owns

• Equipment row cleaning and normalization
• Canonicalization of equipment dimensions such as brand, model, unit, category, acquisition mode, and DCP year
• Review-driven DCP attribution normalization layered on top of lookups.db
• Review-driven equipment location normalization for classroom, lab, office, and other storage/use contexts
• Review-driven supplier and disposition-status normalization for common vendor and asset-state variants
• Entity import from the upstream entity artifact
• Linking accountable personnel using a personnel snapshot artifact
• Equipment views, dimension issue audits, and DCP cross-validation audit views

This package owns the equipment database contract only.

Inputs And Outputs

Required inputs:

• an equipment CSV export
• a lookup database produced by deped-template
• an entity database produced by deped-entity
• a personnel database produced by deped-hr

Optional input:

• the DCP procurement dataset (deped-dataset/db.sqlite3) — enables cross-validation of declared equipment against Central Office procurement contracts

Primary outputs:

• equipment.db
• equipment_dimension_issues.txt

The database stores normalized equipment rows plus canonical dimension tables seeded from the lookup artifact and reviewed dictionaries under src/deped_equipment/dictionaries/. When the DCP dataset is supplied, five dcp_* columns are populated on every equipment row and the dcp_delivery_summary table is seeded.

CLI

Build the equipment database:

uv run equipment build \
  --equipment data/equipment.csv \
  --lookups artifacts/lookups.db \
  --entities-db artifacts/entities.db \
  --personnel-db artifacts/personnel.db \
  --db artifacts/equipment.db

Build with DCP cross-validation (populates dcp_* columns and audit views):

uv run equipment build \
  --equipment data/equipment.csv \
  --lookups artifacts/lookups.db \
  --entities-db artifacts/entities.db \
  --personnel-db artifacts/personnel.db \
  --dcp-dataset-db ../deped-dataset/db.sqlite3 \
  --db artifacts/equipment.db

Run the lightweight audit command:

uv run --package deped-equipment deped-equipment audit \
  --db artifacts/equipment.db

justfile Recipes

Recipe	Description
just build	Build equipment.db without DCP cross-validation
just build-dcp	Build equipment.db with DCP cross-validation
just prototype	Run the DCP prototype inspection script (Phase 1 sample validation)
just restart	Clean artifacts/ and rebuild from scratch (no DCP)
just restart-dcp	Clean artifacts/ and rebuild from scratch with DCP
just lookup	Regenerate lookups.db from templates

DCP Cross-Validation

Central Office DCP procurement (2018–2025) is the authoritative record for centrally-delivered equipment. When --dcp-dataset-db is supplied, each equipment row is matched against the procurement contracts and five columns are written: dcp_cost, dcp_year, dcp_component, dcp_match_type, dcp_supplier.

Three audit views expose the results:

• v_audit_dcp_reported_cost — cost accuracy (overstated / understated / not declared)
• v_audit_dcp_unit_coverage — unit completeness per delivery (none declared / underdeclared / overdeclared)
• v_audit_dcp_condition — serviceability of confirmed-delivered equipment

Before running a full build, the prototype script (scripts/dcp_prototype.py) can validate the matching logic against 19 sample schools:

just prototype

This produces artifacts/dcp_prototype.db for inspection. See docs/dcp-cross-validation.md for the full matching logic and sample queries.

Nuances

• Entity loading is artifact-driven. This package imports entities from entities.db using the same upstream contract as deped-hr.
• Personnel matching is artifact-driven. This package reads a snapshot from personnel.db and reuses shared helpers from deped-hr and deped-dcp-template where appropriate.
• Link resolution uses multiple signals, including scoped employee counts and name matching. Ambiguous matches are preserved as ambiguous rather than guessed.
• Equipment rows whose natural key is missing from the upstream entity artifact are flagged in equipment_dimension_issues.txt and skipped rather than aborting the build.
• Dimension issues are treated as first-class audit output. Raw values that cannot be canonicalized cleanly are emitted to equipment_dimension_issues.txt.
• DCP attribution is review-driven. Raw DCP Package values stay in dcp_package_raw, while approved canonical values resolve through equipment_dcp_attributions.
• Equipment location is review-driven. Raw equipment_location values remain intact, while approved canonical groupings resolve through equipment_locations.
• Only equipment-owned SQL assets live here now. Retired monolith views and unrelated tables were intentionally removed.

Tests

Run the package tests from this directory:

uv run pytest -q