-- SPDX-License-Identifier: Apache-2.0
--
-- 0009 — tenants_index.db: add `tenants.last_accessed` (v0.9.0 P1).
--
-- Closes the v0.8.0 documentation-vs-code gap (lesson #39, second incident):
-- v0.8.0's release notes referenced a `tenants.last_accessed` column in the
-- registry, but no migration ever added it. This migration adds the column
-- for the first time and wires `TenantRegistry::touch_last_accessed` to
-- populate it on every `get_or_open` (cache miss AND cache hit).
--
-- IMPORTANT — this migration ONLY applies to `<data_dir>/tenants_index.db`.
-- The per-tenant DBs at `<data_dir>/tenants/<id>.db` continue to use the
-- per-tenant migration chain unchanged.
--
-- ## Shape
--
-- Stored as INTEGER ms-since-epoch to mirror the existing `created_at_ms`
-- shape (lesson #1 — every Solo timestamp is INTEGER ms-since-epoch, no
-- mixing TIMESTAMPTZ / TEXT / INTEGER inside one schema). Nullable —
-- existing tenants populated by `0009` get `NULL` and only flip to a real
-- value on their next open (the first `touch_last_accessed` call). New
-- tenants registered post-0009 land with NULL until first open, same path.
--
-- ## Idempotency
--
-- SQLite doesn't support `ALTER TABLE ... ADD COLUMN IF NOT EXISTS` natively.
-- We rely on the migration runner's tracker (`schema_migrations_tenants_index`)
-- to skip this file on re-runs — same pattern as 0008. Manually running this
-- SQL twice against a fresh DB would fail on the second run (column already
-- exists), but the runner never replays an applied migration.
--
-- ## Why no HNSW shared rowid (lesson #23)
--
-- `last_accessed` is INTEGER ms-since-epoch, not a rowid surrogate. No HNSW
-- namespace overlap risk.

ALTER TABLE tenants ADD COLUMN last_accessed INTEGER;

-- No index: `last_accessed` is written on every `get_or_open` and read by
-- `solo tenants list`. The list query is O(n) over all tenants regardless
-- (`ORDER BY created_at_ms`); an index on `last_accessed` would only help
-- a future "show tenants accessed in the last N days" query that doesn't
-- exist yet. Add the index if/when that query lands.
