---
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.