Suriyakumarvijayanayagam
5453c26e4c
Migrations were run by hand and nothing recorded which had run, so re-running the setup script against an existing database failed on the first CREATE TABLE, and shipping a new migration gave an operator no way to know whether an estate had it. A missed migration is not a startup error - it is a query referencing a column that is not there, surfacing later on whichever endpoint touches it first. server/internal/migrate applies pending migrations at boot and refuses to start against a schema it does not match. One transaction per file holding both the DDL and the row that records it; an advisory lock so two servers starting at once cannot both apply 008; checksums so an edited migration is refused by name rather than silently skipped; numeric ordering so 010 does not run before 009. `migrate -baseline N` adopts a database built before any of this existed, because "the clients table exists" does not say whether 007's index does. Verified on the live database: adopted 001-007, applied 008. 008 adds two indexes on `purchases`, found by asking the database which foreign keys had nothing behind them and then checking what queries the table. The conversion report filters client_id + occurred_at, which is exactly the estate-wide case with no site to narrow it. run-local.sh had two bugs, both found by running it rather than reading it: it reused a broker container whose bind mount pointed at a directory that no longer existed, and it discarded stderr on the mosquitto_passwd call, so under `set -e` it exited at step 5 with no output at all. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01HViLj9gYNRtSr7YVZmW5sn
28 lines
1.2 KiB
PL/PgSQL
28 lines
1.2 KiB
PL/PgSQL
-- Two indexes on `purchases`, both for queries that already exist.
|
|
--
|
|
-- Found by asking the database which foreign keys had no index behind them and
|
|
-- then checking what actually queries the table, rather than by adding indexes
|
|
-- on principle: every one of them costs a write on the path that records a
|
|
-- sale.
|
|
--
|
|
-- 1. The conversion report filters `client_id` + `occurred_at`, with the site
|
|
-- optional - an owner comparing shops is the whole reason that report
|
|
-- exists, and that is precisely the case with no site to narrow it. The
|
|
-- existing purchases_site_time_idx cannot serve it. Today the table has a
|
|
-- handful of rows and a sequential scan is free; purchases is the table
|
|
-- that grows with a shop's trade, so this is the one that stops being free.
|
|
--
|
|
-- 2. purchases.visit_id is a foreign key with nothing behind it. Every delete
|
|
-- of a visit has to prove no purchase references it, which without an index
|
|
-- is a full scan per row - and erasing a customer deletes their visits.
|
|
|
|
BEGIN;
|
|
|
|
CREATE INDEX IF NOT EXISTS purchases_client_time_idx
|
|
ON purchases (client_id, occurred_at DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS purchases_visit_idx
|
|
ON purchases (visit_id);
|
|
|
|
COMMIT;
|