Observations filtered by instinct skip the cold tier, and the response cannot say so

[ssdavidai]

#657 added ?instinct= to GET /api/v1/state/observations. instinct_ref is a hot-only column — it is not a bare column on archive_observation — so the implementation sets hotOnlyFilters and skips the cold tier entirely whenever that filter is present.

Correct today: all 5,904 observations on the live tenant are hot-tier, so nothing is missed.

The problem is what it looks like later. Once observations age into cold.db, an instinct's panel will show only its hot observations and present that as the full count. Under-reporting how often a pattern has been seen is not a neutral error — the observation count feeds how much discretion an instinct earns, and the page is where you judge whether to grant it autonomy.

coldRead.ts's own header warns about exactly this: a query whose window reaches past the cutoff "must read both tiers or it silently" under-reports. This change adds a new path that does so deliberately.

The response cannot currently distinguish the two cases. tiers comes back as ["hot"] both when cold was out of scope and when cold was deliberately skipped because of a hot-only filter. A caller cannot tell "there is nothing older" from "I did not look".

Options, roughly in order of appeal:

1. Add instinct_ref as a real column on archive_observation so the filter works across both tiers. Needs a migration (phase0).
2. Have the response distinguish the cases — e.g. tiers_skipped: ["cold"] — so a consumer can disclose the limit rather than silently truncate.
3. Decompress and filter cold bodies. O(n) and unattractive.

Not urgent. Filed so the daily-cadence workaround is not mistaken for a design decision, and so the first person to see a suspiciously low observation count has something to find.

Related: #584.

[github-actions]

This issue is missing prioritization dimensions, so it will not
appear correctly in Now/Next/Later portfolio views.

#658 Observations filtered by instinct skip the cold tier, and the resp
    missing:     kind, impact, effort, priority, horizon, customer, theme, area

0/1 open issues fully classified.

Rubric: docs/ISSUE-TAXONOMY.md

[ssdavidai]

Foreman spec — make instinct-filtered observations truly cross-tier

Decision

Use the durable fix: promote instinct_ref to a plain, indexed column on archive_observation. Do not keep the current hot-only workaround and do not decompress the entire cold archive to evaluate the filter.

LANES

Lane A — cold schema compatibility

• Extend packages/ctrl/src/db/cold.ts so new archive_observation tables contain nullable instinct_ref TEXT.
• Add an index suitable for instinct_ref = ? with ts DESC paging.
• Add an idempotent startup migration for existing cold.db files. CREATE TABLE IF NOT EXISTS is insufficient: inspect the table and ALTER TABLE archive_observation ADD COLUMN instinct_ref TEXT only when absent, then create the index.
• Existing archived rows may initially have NULL; the migration must not rebuild, drop, or corrupt the archive.

Lane B — compaction contract

• Update the observation TablePlan in packages/ctrl/src/db/compactor.ts so every newly archived observation copies instinct_ref from the hot row into the plain cold column while preserving the compressed full-row body.
• Preserve the current copy-before-delete / overlap-safe compaction behavior.

Lane C — cross-tier reader and route

• Add instinct_ref to the observation filterCols in packages/ctrl/src/db/coldRead.ts.
• Remove the observation route's use of hotOnlyFilters; pass the normalized instinct path as a normal equality filter.
• Preserve current normalization (slug, instinct/slug, and instinct/slug.md all map to instinct/slug.md), global ts DESC order, pagination, overlap de-duplication, and count semantics.
• With a window reaching the cutoff, GET /api/v1/state/observations?instinct=<slug> must read both eligible tiers. tiers, total, and entries must describe the work actually performed; there must be no silent truncation.

Lane D — regression coverage and docs

• Replace the hot-only assumptions in packages/ctrl/tests/observation-instinct-filter.test.ts with mixed hot/cold fixtures.
• Add cold-schema migration coverage for an already-existing archive lacking instinct_ref.
• Update storage comments/docs that enumerate observation archive filter columns.

CONTRACTS

API contract

GET /api/v1/state/observations?instinct=<slug|path> remains backward-compatible:

• response shape stays { observations, count, total, tiers };
• total is the de-duplicated matching count across all in-scope tiers;
• tiers reports tiers that actually contributed rows under existing semantics;
• since may legitimately keep the query hot-only when the cold cutoff is out of scope, but the presence of instinct alone must never suppress cold reads.

Storage contract

• archive_observation.instinct_ref is nullable text and contains the canonical stored vault path when present.
• New compactions populate it directly from observation.instinct_ref.
• Legacy cold rows with NULL remain readable and do not cause false positive matches. No O(n) body inflation is introduced.

Migration contract

• Opening a fresh or legacy cold.db is safe and idempotent.
• Repeated startup does not duplicate columns or fail on an existing index.
• No state.db migration is required; the hot observation table already owns instinct_ref.

Acceptance criteria

1. A matching instinct with one hot and one cold observation returns both, ordered newest-first, with total = 2 and no duplicate if a crash-straddle row exists in both tiers.
2. Non-matching cold observations are excluded without body-wide decompression.
3. Bare slug, prefix path, and canonical .md path forms produce identical results.
4. kind, semantic status=unprocessed, since/until, limit, and offset continue composing with instinct using AND semantics.
5. A recent since bound that cannot reach cold does not open/query cold; an unbounded instinct query does.
6. A legacy cold database created without instinct_ref upgrades in place and can archive/query new instinct-bearing rows afterward.
7. Existing cross-tier suites plus focused observation filter/compactor/migration tests pass.

Smoke plan

• Run the focused ctrl tests for cold schema/compaction/cross-tier reads and observation-instinct-filter.test.ts.
• In a temp data dir, create a legacy cold.db schema without instinct_ref, open it through getColdDb(), verify the column/index appears, compact an old instinct-bearing observation, and query by each accepted instinct form.
• Assert response evidence for mixed tiers: ids, total, tiers, ordering, and overlap de-duplication.
• Include a ## Smoke evidence section in the PR describing commands and real outputs; pr-review-gate.yml requires it.

Suggested taxonomy: kind=bug, impact=medium, effort=medium, priority=P2, horizon=next, customer=internal, theme=reliability, area=ctrl-api/storage.