stash init scaffolds timestamp/timestamptz columns as cast_as: 'date', truncating time-of-day at encrypt

[tobyhede]

Found while investigating #779 (see #779 (comment)). Unrelated to that issue's decrypt-side question — this is encrypt-side, and it destroys data rather than mistyping it.

The behaviour

stash init scaffolds a timestamptz / timestamp column as a Date* domain, whose cast_as: 'date' truncates time-of-day to midnight at encrypt time. The scaffold cannot propose a Timestamp* domain at all, because the type that feeds the picker has no 'timestamp' member.

The chain:

1. packages/cli/src/commands/init/types.ts:7 — export type DataType = 'string' | 'number' | 'boolean' | 'date' | 'json'. No 'timestamp'.
2. packages/cli/src/commands/init/lib/introspect.ts:33-36 — pgTypeToDataType maps date, timestamp and timestamptz all to 'date'. Pinned at lib/__tests__/introspect.test.ts:38: expect(pgTypeToDataType('timestamptz')).toBe('date').
3. introspect.ts:183-192 — candidateDomains('date') returns exactly Date, DateEq, DateOrd. No Timestamp* option exists in the list.
4. introspect.ts:283-296 — the picker offers only those, defaulting to the widest (DateOrd via defaultDomain). The prompt reads Encryption domain for "created_at" (date)? — the only signal that anything was collapsed, and it reads as a description rather than a warning.
5. packages/stack/src/schema/internal.ts:47-53 — cast_as: 'date' "is calendar-date only (time-of-day truncated to midnight), while timestamp preserves the full date+time. v3 timestamp domains set cast_as: 'timestamp' so the FFI keeps the instant."

So a user who accepts the scaffolded schema for a created_at timestamptz column encrypts midnight, forever, and the original time-of-day is not recoverable from the ciphertext.

Why this isn't just the documented collapse

init/types.ts:8-15 already declares the narrowing deliberate:

The scaffold offers the subset below; the full numeric/date lattice (Smallint/Bigint/Numeric/Real/Double/Timestamp) is left to the user's real schema files, exactly as pgTypeToDataType collapses those types today.

That reasoning is sound for the numeric half and does not transfer to the date half. Scaffolding int8 as Integer is a capability and precision choice the user can inspect and widen later — the stored value is still the value. Scaffolding timestamptz as DateOrd is a lossy write: the data is gone at encrypt time, before anyone notices the domain was too narrow. Same sentence, two very different consequences, and the docblock treats them as equivalent.

The asymmetry is what makes this worth fixing rather than documenting: every other entry in that collapsed lattice is recoverable by editing the schema and re-encrypting from plaintext. This one isn't, because the plaintext is what got truncated.

Suggested fix

Add 'timestamp' to DataType, map timestamp / timestamptz to it in pgTypeToDataType (leaving bare date → 'date'), and give candidateDomains('timestamp') the Timestamp / TimestampEq / TimestampOrd triple. V3Domain already needs the three new members; internal.ts already has the cast_as support, so nothing downstream of the scaffold changes.

Points to get right:

• introspect.test.ts:38 pins the current mapping and must be updated deliberately, not deleted — it is the regression test for this.
• introspect.test.ts:131 iterates ['text', 'int4', 'jsonb', 'bool', 'timestamptz']; check what it asserts about timestamptz before changing the mapping.
• defaultDomain reads the last entry of the candidate list as "widest", so order the new triple narrowest→widest like the others (Timestamp, TimestampEq, TimestampOrd).
• Re-running stash init on an already-encrypted DB is meant to be a no-op by default (introspect.ts:~270, pre-selects columns that already carry an EQL domain) — verify a column already scaffolded as DateOrd is not silently re-proposed as TimestampOrd, which would look like a fix but would be a second migration.
• skills/stash-cli describes the init flow, and skills/stash-encryption:247 documents the Date vs Timestamp plaintext distinction. Check both against whatever lands.

Not in scope here

Whether existing scaffolded deployments can be repaired. They generally can't from ciphertext — if the source plaintext column is still present, a re-encrypt fixes it; if the rollout already dropped it, the time-of-day is gone. Worth a note in the changeset either way.