Why does SQLite refuse an index on a non-deterministic expression?

Index on a non-deterministic expression

Critical: SQLite refuses the statement, so the migration fails here.

What happens

An index expression must give the same answer for the same row every time, so SQLite refuses random(), changes(), last_insert_rowid() and their kin in CREATE INDEX: "non-deterministic functions prohibited in index expressions". The statement fails and the migration stops.

Why it is dangerous on a populated table

SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.

Fires on

CREATE INDEX orders_shuffle ON orders (id + random());

The safe pattern

CREATE INDEX orders_email_ci ON orders (lower(email));

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

CREATE INDEX orders_changes ON orders (changes());

CREATE INDEX orders_shuffle ON orders (id + random());

CREATE INDEX orders_email_ci ON orders (lower(email));

CREATE INDEX orders_status ON orders (status);

How to check locally

npx bolvrk check migration.sql --engine=sqlite