49 lines
3.0 KiB
Markdown
49 lines
3.0 KiB
Markdown
---
|
|
name: Phase 1 task 1.3 decisions
|
|
description: positions hypertable schema divergences from spec, cross-check against processor migration, and assertion block patterns
|
|
type: project
|
|
---
|
|
|
|
Task 1.3 authored three SQL migrations under directus/db-init/. The critical finding was a substantial divergence between the task spec (03-initial-migrations.md) and the processor's actual migration (processor/src/db/migrations/0001_positions.sql). The processor migration wins in all cases.
|
|
|
|
**Why:** The processor is the sole writer for positions. If the table schema doesn't match what the processor inserts, writes fail at runtime with NOT NULL violations or column-not-found errors.
|
|
|
|
**How to apply:** Always read processor/src/db/migrations/0001_positions.sql before writing or modifying the positions table schema. Do not trust 03-initial-migrations.md column list without cross-checking.
|
|
|
|
## Divergences found (spec vs. processor ground truth)
|
|
|
|
1. **ingested_at** — not in spec, present in processor migration as `timestamptz NOT NULL DEFAULT now()`. Required.
|
|
2. **codec** — not in spec, present in processor migration as `text NOT NULL`. Required.
|
|
3. **altitude/angle/speed** — spec says `DOUBLE PRECISION nullable`; processor has `real NOT NULL`. Use `real NOT NULL`.
|
|
4. **satellites/priority** — spec says nullable; processor has `NOT NULL`. Use NOT NULL.
|
|
5. **attributes DEFAULT** — spec adds `DEFAULT '{}'::jsonb`; processor has no default. No default in the migration (processor always supplies the value).
|
|
6. **PRIMARY KEY vs UNIQUE INDEX** — spec uses `PRIMARY KEY (device_id, ts)`; processor uses `CREATE UNIQUE INDEX positions_device_ts ON positions (device_id, ts)` (no PK). TimescaleDB idiomatic: unique index, no PK. Index name is `positions_device_ts` (no `_idx` suffix).
|
|
7. **Chunk interval** — spec says `INTERVAL '7 days'`; processor uses `INTERVAL '1 day'`. Use 1 day.
|
|
8. **Indexes** — spec has one composite `(device_id, ts DESC)` index; processor has two: `positions_device_ts (device_id, ts)` (unique) and `positions_ts (ts DESC)`. Both are required.
|
|
|
|
## Assertion block pattern established
|
|
|
|
Each migration ends with a `DO $$ DECLARE ... BEGIN ... END $$;` block that:
|
|
- Checks table/column existence via `information_schema.columns`
|
|
- Checks hypertable registration via `timescaledb_information.hypertables`
|
|
- Checks index existence via `pg_indexes`
|
|
- Checks index predicate via `pg_indexes.indexdef ILIKE '%where (faulty = false)%'`
|
|
- Raises named exceptions on any mismatch
|
|
|
|
For the faulty column NOT NULL check, the pattern is:
|
|
```sql
|
|
SELECT data_type, is_nullable = 'NO', column_default
|
|
INTO _col_type, _col_notnull, _col_default
|
|
FROM information_schema.columns ...
|
|
```
|
|
|
|
The column_default for `DEFAULT FALSE` is stored by Postgres as the string `'false'` (lower-case, no quotes) in information_schema.
|
|
|
|
## Files created
|
|
|
|
- directus/db-init/001_extensions.sql
|
|
- directus/db-init/002_positions_hypertable.sql
|
|
- directus/db-init/003_faulty_column.sql
|
|
|
|
All three are append-only once applied. The runner's checksum guard (exit 2) enforces this.
|