feat(db): nspd_quarter_dumps table for PKK harvest persistence (#94 pt.3/4) #110

Merged

lekss361 merged 1 commit from feat/nspd-quarter-dumps-schema into main

2026-05-12 15:22:50 +00:00

lekss361 commented

2026-05-12 15:20:15 +00:00

(Migrated from github.com)

Summary

Sprint 1.1 item #2 из 4-частного плана #94 part 2 — persistent storage для QuarterDump snapshots (PR #109 уже merged).

Foundation для:

Sprint 1.1 item #3 (next PR): Celery harvest_quarter task + beat schedule — пишет dumps батчами
Sprint 1.1 item #4 (after): analyze_parcel integration — читает квартал из этой таблицы, не делая NSPD HTTP запросов в request-цикле

Schema (data/sql/88_nspd_quarter_dumps.sql)

Table `nspd_quarter_dumps` — 17 columns matching `QuarterDump` dataclass

Column	Type	Note
`quarter_cad`	TEXT PRIMARY KEY	Natural key, one row per quarter
`quarter_geom`	geometry(MultiPolygon, 4326)	WGS84, transformed from NSPD 3857
`bbox_3857`	geometry(Polygon, 3857)	Stored untransformed для scheduler queries
`parcels_count` / ... / `risks_count`	INT NOT NULL DEFAULT 0	7 per-layer counts
`total_features`	INT NOT NULL DEFAULT 0	Computed denorm (QuarterDump.total_features)
`features_json`	JSONB NOT NULL DEFAULT '[]'	Array of ~117 NSPDFeature objects, ~120KB per row (TOAST)
`layers_fetched`	TEXT[] NOT NULL DEFAULT '{}'	Mirrors QuarterDump.layers_fetched tuple
`fetched_at_utc`	TIMESTAMPTZ NOT NULL	Harvest timestamp
`harvest_duration_ms`	INT (nullable)	NULL = "not measured", не "zero duration"
`harvest_error`	TEXT (nullable)	WAF/timeout/etc error при failed harvest
`region_code`	SMALLINT NOT NULL	No DEFAULT — explicit per insert

6 indexes

Index	Type	Use case
PK	UNIQUE B-tree on `quarter_cad`	analyze_parcel direct lookup
`bbox_gist`	GIST on `bbox_3857`	Spatial scheduler "find dumps in bbox X"
`geom_gist`	GIST on `quarter_geom`	Joins с PZZ/districts в 4326
`fetched_idx`	B-tree on `fetched_at_utc DESC`	Freshness queries (re-harvest stale dumps)
`region_idx`	B-tree on `region_code`	Multi-region filter
`healthy_idx`	Partial B-tree `WHERE harvest_error IS NULL`	analyze_parcel cache lookups

View `v_quarter_dumps_freshness`

Admin monitoring helper: quarter_cad, region_code, fetched_at_utc, total_features, harvest_error, AGE(NOW(), fetched_at_utc) AS age, harvest_error IS NOT NULL AS is_failed. Backed by fetched_idx.

Conventions

BEGIN; ... COMMIT; atomic transaction
CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS — idempotent re-applicable
COMMENT ON TABLE + 7 COMMENT ON COLUMN + comments на каждом index
psycopg v3 compatible (DDL only)
numbered 88_ (после 86 v_bucket_success_score, 87 on_demand_indexes)

Applied to prod

DDL applied к prod в рамках database-expert verification (idempotent, безопасно для re-run). Файл — canonical source.

EXPLAIN sanity (prod, empty table)

WHERE quarter_cad = '66:41:0204016' → Index Scan on PK
WHERE ST_Intersects(bbox_3857, ST_MakeEnvelope(...,3857)) → Index Scan via GIST
WHERE fetched_at_utc < NOW() - INTERVAL '30 days' → Seq Scan на пустой таблице; planner переключится на B-tree после ~50 rows

Vault

code/schemas/Schema_Nspd_Quarter_Dumps.md — full schema description, indexes, EXPLAIN, upsert template с ON CONFLICT DO UPDATE covering all columns (per Bug_Cad_Buildings_OnConflict_PartialUpdate_Apr30 convention).

Code review (pre-push, code-reviewer agent)

Verdict: APPROVE, 0 blocking, 2 minor non-blocking observations:

MINOR-1 quarter_geom MultiPolygon vs NSPD plain Polygon — резолвится в Sprint 1.1 #3 PR (Celery task) через ST_Multi(ST_Transform(...)) на ingest contract. Schema strict как guardrail.
MINOR-2 layers_fetched TEXT[] DEFAULT '{}' — documented в comment как partial-failure path для error rows (когда harvest упал до фактических fetches).

Acceptance

Table nspd_quarter_dumps создана с 17 columns
6 indexes (PK + 2 GIST + 3 B-tree + 1 partial)
View v_quarter_dumps_freshness
EXPLAIN sanity ✓
Vault entry создан
Idempotent + atomic
Code-reviewer APPROVE

Test plan

Migration applied к prod (DDL only, idempotent)
Schema match QuarterDump dataclass (PR #109)
After Sprint 1.1 #3 (Celery PR): smoke INSERT через harvest_quarter task → verify total_features > 0
After Sprint 1.1 #4 (analyze integration): verify analyze_parcel использует cached dump вместо NSPD HTTP

Sequential rule — следующий PR (Celery task) создаётся ТОЛЬКО после merge этого.

Part of #94.

## Summary **Sprint 1.1 item #2** из 4-частного плана #94 part 2 — persistent storage для `QuarterDump` snapshots (PR #109 уже merged). Foundation для: - **Sprint 1.1 item #3** (next PR): Celery `harvest_quarter` task + beat schedule — пишет dumps батчами - **Sprint 1.1 item #4** (after): `analyze_parcel` integration — читает квартал из этой таблицы, не делая NSPD HTTP запросов в request-цикле ## Schema (data/sql/88_nspd_quarter_dumps.sql) ### Table `nspd_quarter_dumps` — 17 columns matching `QuarterDump` dataclass | Column | Type | Note | |---|---|---| | `quarter_cad` | TEXT PRIMARY KEY | Natural key, one row per quarter | | `quarter_geom` | geometry(MultiPolygon, 4326) | WGS84, transformed from NSPD 3857 | | `bbox_3857` | geometry(Polygon, 3857) | Stored untransformed для scheduler queries | | `parcels_count` / ... / `risks_count` | INT NOT NULL DEFAULT 0 | 7 per-layer counts | | `total_features` | INT NOT NULL DEFAULT 0 | Computed denorm (QuarterDump.total_features) | | `features_json` | JSONB NOT NULL DEFAULT '[]' | Array of ~117 NSPDFeature objects, ~120KB per row (TOAST) | | `layers_fetched` | TEXT[] NOT NULL DEFAULT '{}' | Mirrors QuarterDump.layers_fetched tuple | | `fetched_at_utc` | TIMESTAMPTZ NOT NULL | Harvest timestamp | | `harvest_duration_ms` | INT (nullable) | NULL = "not measured", не "zero duration" | | `harvest_error` | TEXT (nullable) | WAF/timeout/etc error при failed harvest | | `region_code` | SMALLINT NOT NULL | No DEFAULT — explicit per insert | ### 6 indexes | Index | Type | Use case | |---|---|---| | PK | UNIQUE B-tree on `quarter_cad` | analyze_parcel direct lookup | | `bbox_gist` | GIST on `bbox_3857` | Spatial scheduler "find dumps in bbox X" | | `geom_gist` | GIST on `quarter_geom` | Joins с PZZ/districts в 4326 | | `fetched_idx` | B-tree on `fetched_at_utc DESC` | Freshness queries (re-harvest stale dumps) | | `region_idx` | B-tree on `region_code` | Multi-region filter | | `healthy_idx` | Partial B-tree `WHERE harvest_error IS NULL` | analyze_parcel cache lookups | ### View `v_quarter_dumps_freshness` Admin monitoring helper: `quarter_cad`, `region_code`, `fetched_at_utc`, `total_features`, `harvest_error`, `AGE(NOW(), fetched_at_utc) AS age`, `harvest_error IS NOT NULL AS is_failed`. Backed by `fetched_idx`. ## Conventions - `BEGIN; ... COMMIT;` atomic transaction - `CREATE TABLE IF NOT EXISTS` + `CREATE INDEX IF NOT EXISTS` — idempotent re-applicable - `COMMENT ON TABLE` + 7 `COMMENT ON COLUMN` + comments на каждом index - psycopg v3 compatible (DDL only) - numbered `88_` (после 86 v_bucket_success_score, 87 on_demand_indexes) ## Applied to prod DDL applied к prod в рамках database-expert verification (idempotent, безопасно для re-run). Файл — canonical source. ## EXPLAIN sanity (prod, empty table) - `WHERE quarter_cad = '66:41:0204016'` → Index Scan on PK - `WHERE ST_Intersects(bbox_3857, ST_MakeEnvelope(...,3857))` → Index Scan via GIST - `WHERE fetched_at_utc < NOW() - INTERVAL '30 days'` → Seq Scan на пустой таблице; planner переключится на B-tree после ~50 rows ## Vault `code/schemas/Schema_Nspd_Quarter_Dumps.md` — full schema description, indexes, EXPLAIN, **upsert template** с `ON CONFLICT DO UPDATE` covering all columns (per `Bug_Cad_Buildings_OnConflict_PartialUpdate_Apr30` convention). ## Code review (pre-push, code-reviewer agent) Verdict: **APPROVE**, 0 blocking, 2 minor non-blocking observations: - **MINOR-1** `quarter_geom MultiPolygon` vs NSPD plain `Polygon` — **резолвится в Sprint 1.1 #3 PR** (Celery task) через `ST_Multi(ST_Transform(...))` на ingest contract. Schema strict как guardrail. - **MINOR-2** `layers_fetched TEXT[] DEFAULT '{}'` — documented в comment как partial-failure path для error rows (когда harvest упал до фактических fetches). ## Acceptance - [x] Table `nspd_quarter_dumps` создана с 17 columns - [x] 6 indexes (PK + 2 GIST + 3 B-tree + 1 partial) - [x] View `v_quarter_dumps_freshness` - [x] EXPLAIN sanity ✓ - [x] Vault entry создан - [x] Idempotent + atomic - [x] Code-reviewer APPROVE ## Test plan - [x] Migration applied к prod (DDL only, idempotent) - [x] Schema match `QuarterDump` dataclass (PR #109) - [ ] After Sprint 1.1 #3 (Celery PR): smoke INSERT через `harvest_quarter` task → verify `total_features > 0` - [ ] After Sprint 1.1 #4 (analyze integration): verify `analyze_parcel` использует cached dump вместо NSPD HTTP Sequential rule — следующий PR (Celery task) создаётся ТОЛЬКО после merge этого. Part of #94.

lekss361 commented

2026-05-12 15:22:37 +00:00

(Migrated from github.com)

✅ Auto-review passed (LGTM) — pt.3/4, schema чист и идемпотентен.

Schema correctness:

BEGIN/COMMIT атомарно, все IF NOT EXISTS / CREATE OR REPLACE VIEW — идемпотентно.
Sequential numbering 88 после 87 (PR #109) корректно.
features_json JSONB DEFAULT '[]' + NOT NULL — safe для empty harvests.
layers_fetched TEXT[] DEFAULT '{}' правильный empty array literal.
region_code SMALLINT NOT NULL без DEFAULT — caller обязан supply, NOT NULL защищает.

Performance:

GIST на bbox_3857 и quarter_geom — correct.
B-tree на fetched_at_utc DESC для staleness queries.
Partial index WHERE harvest_error IS NULL — deliberate optimization для healthy-dump queries.

Data model match с QuarterDump (PR #109):

quarter_cad PK TEXT, layers_fetched TEXT[], bbox_3857 geometry(Polygon, 3857), fetched_at_utc TIMESTAMPTZ — все совпадают.
search_features + layer_features collapsed в features_json JSONB с "layer" discriminator key — valid denormalization, no data loss.
Денормализованные count columns матчат QuarterDump aggregation properties.

Minor observation (non-blocker):

v_quarter_dumps_freshness:183 — ORDER BY внутри view body не гарантируется PostgreSQL при wrapping в subquery/CTE. Здесь view admin/monitoring only, SELECT * работает как ожидается, но future developer должен знать.

Merging.

✅ Auto-review passed (LGTM) — pt.3/4, schema чист и идемпотентен. **Schema correctness:** - `BEGIN/COMMIT` атомарно, все `IF NOT EXISTS` / `CREATE OR REPLACE VIEW` — идемпотентно. - Sequential numbering 88 после 87 (PR #109) корректно. - `features_json JSONB DEFAULT '[]'` + `NOT NULL` — safe для empty harvests. - `layers_fetched TEXT[] DEFAULT '{}'` правильный empty array literal. - `region_code SMALLINT NOT NULL` без DEFAULT — caller обязан supply, NOT NULL защищает. **Performance:** - GIST на `bbox_3857` и `quarter_geom` — correct. - B-tree на `fetched_at_utc DESC` для staleness queries. - Partial index `WHERE harvest_error IS NULL` — deliberate optimization для healthy-dump queries. **Data model match с QuarterDump (PR #109):** - `quarter_cad` PK TEXT, `layers_fetched TEXT[]`, `bbox_3857` geometry(Polygon, 3857), `fetched_at_utc TIMESTAMPTZ` — все совпадают. - `search_features` + `layer_features` collapsed в `features_json JSONB` с `"layer"` discriminator key — valid denormalization, no data loss. - Денормализованные count columns матчат QuarterDump aggregation properties. **Minor observation (non-blocker):** - `v_quarter_dumps_freshness:183` — `ORDER BY` внутри view body не гарантируется PostgreSQL при wrapping в subquery/CTE. Здесь view admin/monitoring only, `SELECT *` работает как ожидается, но future developer должен знать. Merging.