Revision b8e4f2a6c1d3 is now retryable from an interrupted SQLite upgrade at
7c3d9e1f2a4b. It does not assume that the migration either fully ran or did
nothing.

What the reported database state means #

The reported source table is still authoritative: product_types has only
id, name, and rules. _alembic_tmp_product_types is an Alembic batch
output table. In the inspected copy it had the expected expanded schema and
zero rows. The new technology/configuration tables existed with compatible
schemas and no rows.

The original revision first batch-rebuilt product_types, then rebuilt
operations and materials, backfilled stable codes, and created seven
technology/configuration tables. SQLite can leave DDL behind when a process
dies before Alembic updates alembic_version; app model create_all() can
also pre-create future tables before the revision is stamped. Therefore table
existence alone does not prove which migration statements ran. The repair
validates the schema and table contents instead of guessing based on presence.

The revised migration:

• uses additive columns instead of SQLite batch rebuilds;
• skips only already-present columns of compatible types;
• creates required unique/index structures only when missing, and rejects
  conflicting indexes or duplicate codes;
• preserves existing technology/configuration tables and their rows after
  checking required columns, types, keys, and relationships;
• recognizes only the known _alembic_tmp_product_types,
  _alembic_tmp_operations, or _alembic_tmp_materials outputs;
• removes a known temporary table only if its schema is the expected staged
  schema and every staged row duplicates a legacy source row with only
  migration defaults. An empty staged table is safe to remove. Unexpected or
  non-default staged data aborts with an error and remains untouched;
• aborts if any unrecognized _alembic_tmp_% table remains.

The version row remains at 7c3d9e1f2a4b until Alembic completes the revision.
Do not manually stamp the database.

One-time production recovery #

Deploy the migration code containing the recovery changes first. Do not run
the old application checkout’s migration against the partial database. Then
run this maintenance procedure on the production host. It leaves Caddy and
the database contents alone; stop the backend to avoid concurrent writes.

cd /root/signage-estimator
systemctl stop signage-estimator-backend.service

# Make and verify a fresh SQLite backup using the documented production
# backup procedure in deploy/DEPLOYMENT.md before proceeding.

set -a
. ./.env.production
set +a

.venv/bin/alembic -c backend/alembic.ini current
.venv/bin/alembic -c backend/alembic.ini upgrade b8e4f2a6c1d3
.venv/bin/alembic -c backend/alembic.ini current

.venv/bin/python - <<'PY'
import os, sqlite3
from sqlalchemy.engine import make_url
path=make_url(os.environ['DATABASE_URL']).database
db=sqlite3.connect(f'file:{path}?mode=ro',uri=True)
assert db.execute('select version_num from alembic_version').fetchone()[0]=='b8e4f2a6c1d3'
assert db.execute('pragma integrity_check').fetchone()[0]=='ok'
assert not db.execute('pragma foreign_key_check').fetchall()
assert not db.execute("select name from sqlite_master where type='table' and name like '_alembic_tmp_%'").fetchall()
for table,required in {
    'product_types':{'code','description','active','sort_order','created_at','updated_at'},
    'materials':{'code','price_unit'},
    'operations':{'code','unit','setup_time_min','notes'},
}.items():
    actual={row[1] for row in db.execute(f'pragma table_info("{table}")')}
    assert required <= actual, (table, required-actual)
print('revision, integrity, foreign keys, temporary tables, and required columns verified')
db.close()
PY

systemctl start signage-estimator-backend.service
systemctl status signage-estimator-backend.service --no-pager
curl -fsS http://127.0.0.1:8000/api/health

If alembic upgrade b8e4f2a6c1d3 aborts with an incompatibility message, do not delete
the named table or stamp a revision. Keep the service stopped, retain the
backup and temporary table, and inspect the exact table schema/data reported
by the error before planning a manual recovery.

Validation performed #

The recovery was exercised on a SQLite backup matching the reported state,
on a clean database from base to b8e4f2a6c1d3, and on a normal
7c3d9e1f2a4b database without the later tables. A separate interrupted-state copy containing valid
technology, version, component, operation, parameter, rule, and calculation
configuration rows retained those records through upgrade. The existing
calculation/revision/part/nesting/user/audit/catalog counts and revision
snapshot fingerprint also remained unchanged on the production-like copy.