101 lines
3.6 KiB
Python
101 lines
3.6 KiB
Python
"""phase16 — ensure digest_pending and inspection_id columns on notifications
|
|
|
|
Background
|
|
----------
|
|
The `notifications` table was created before the Alembic migration chain was
|
|
established (pre-phase1 baseline schema). Two columns added to the model
|
|
after the initial creation were never covered by a migration:
|
|
|
|
digest_pending BOOLEAN NOT NULL DEFAULT 0 (used by the digest email system)
|
|
inspection_id INT NULL FK → inspections.id ON DELETE CASCADE
|
|
|
|
Without this migration, any instance whose `notifications` table was created
|
|
from the original baseline (rather than from the current model) will raise
|
|
`OperationalError: Unknown column 'notifications.digest_pending'` the first
|
|
time a notification is created, and the digest email cron will fail entirely.
|
|
|
|
All checks use INFORMATION_SCHEMA so the migration is safe to re-run on any
|
|
MySQL version ≥ 5.7 (CLAUDE.md rules 16, 17).
|
|
|
|
Revision ID: phase16_notifications_columns
|
|
Revises: phase15_audit_log_indexes
|
|
"""
|
|
|
|
revision = 'phase16_notifications_columns'
|
|
down_revision = 'phase15_audit_log_indexes'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
def _column_exists(conn, table, column):
|
|
result = conn.execute(sa.text(
|
|
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS "
|
|
"WHERE TABLE_SCHEMA = DATABASE() "
|
|
"AND TABLE_NAME = :table AND COLUMN_NAME = :col"
|
|
), {"table": table, "col": column})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def _index_exists(conn, table, index_name):
|
|
result = conn.execute(sa.text(
|
|
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS "
|
|
"WHERE TABLE_SCHEMA = DATABASE() "
|
|
"AND TABLE_NAME = :table AND INDEX_NAME = :idx"
|
|
), {"table": table, "idx": index_name})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def upgrade():
|
|
bind = op.get_bind()
|
|
|
|
# 1. digest_pending — Boolean NOT NULL DEFAULT 0
|
|
# Used by notify() to flag notifications for digest delivery, and by
|
|
# send_pending_digests() to find and clear them after delivery.
|
|
if not _column_exists(bind, 'notifications', 'digest_pending'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE notifications "
|
|
"ADD COLUMN digest_pending TINYINT(1) NOT NULL DEFAULT 0"
|
|
))
|
|
|
|
# 2. Index on digest_pending — the digest cron filters on this column
|
|
if not _index_exists(bind, 'notifications', 'ix_notifications_digest_pending'):
|
|
op.execute(sa.text(
|
|
"CREATE INDEX ix_notifications_digest_pending "
|
|
"ON notifications (digest_pending)"
|
|
))
|
|
|
|
# 3. inspection_id — nullable FK to inspections, CASCADE on delete
|
|
# Allows the notification bell to link directly to an inspection.
|
|
if not _column_exists(bind, 'notifications', 'inspection_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE notifications "
|
|
"ADD COLUMN inspection_id INT NULL, "
|
|
"ADD CONSTRAINT fk_notifications_inspection_id "
|
|
" FOREIGN KEY (inspection_id) REFERENCES inspections(id) "
|
|
" ON DELETE CASCADE"
|
|
))
|
|
|
|
|
|
def downgrade():
|
|
bind = op.get_bind()
|
|
|
|
if _column_exists(bind, 'notifications', 'inspection_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE notifications "
|
|
"DROP FOREIGN KEY fk_notifications_inspection_id, "
|
|
"DROP COLUMN inspection_id"
|
|
))
|
|
|
|
if _index_exists(bind, 'notifications', 'ix_notifications_digest_pending'):
|
|
op.execute(sa.text(
|
|
"DROP INDEX ix_notifications_digest_pending ON notifications"
|
|
))
|
|
|
|
if _column_exists(bind, 'notifications', 'digest_pending'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE notifications DROP COLUMN digest_pending"
|
|
))
|