"""initial schema: kit tables + punchlist domain Revision ID: 913bfbbc6c4e Revises: Create Date: 2026-08-20 09:54:43.304922 """ from typing import Sequence, Union from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision: str = '913bfbbc6c4e' down_revision: Union[str, Sequence[str], None] = None branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def upgrade() -> None: """Upgrade schema.""" # ### commands auto generated by Alembic - please adjust! ### op.create_table('bw_grants', sa.Column('username', sa.String(length=64), nullable=False), sa.Column('instance_id', sa.String(length=64), nullable=False), sa.Column('level', sa.String(length=64), nullable=False), sa.PrimaryKeyConstraint('username', 'instance_id') ) op.create_table('bw_instances', sa.Column('id', sa.String(length=64), nullable=False), sa.Column('label', sa.String(length=200), nullable=False), sa.Column('created_by', sa.String(length=64), nullable=True), sa.PrimaryKeyConstraint('id') ) op.create_table('bw_levels', sa.Column('name', sa.String(length=64), nullable=False), sa.Column('permissions', sa.JSON(), nullable=False), sa.Column('assignable', sa.JSON(), nullable=False), sa.PrimaryKeyConstraint('name') ) op.create_table('bw_members', sa.Column('username', sa.String(length=64), nullable=False), sa.Column('level', sa.String(length=64), nullable=False), sa.Column('all_instances', sa.Boolean(), nullable=True), sa.Column('added_by', sa.String(length=64), nullable=True), sa.PrimaryKeyConstraint('username') ) op.create_table('punchlist_seen', sa.Column('punchlist_id', sa.String(length=80), nullable=False), sa.Column('username', sa.String(length=120), nullable=False), sa.Column('last_seen_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('punchlist_id', 'username') ) op.create_table('punchlists', sa.Column('id', sa.String(length=80), nullable=False), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('client_label', sa.String(length=200), nullable=False), sa.Column('client_party_id', sa.String(length=36), nullable=True), sa.Column('external_ref', sa.String(length=80), nullable=True), sa.Column('state', sa.String(length=16), nullable=False), sa.Column('created_by', sa.String(length=120), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_punchlists_external_ref'), 'punchlists', ['external_ref'], unique=False) op.create_table('template_sets', sa.Column('id', sa.String(length=32), nullable=False), sa.Column('key', sa.String(length=80), nullable=False), sa.Column('version', sa.Integer(), nullable=False), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('variables', sa.JSON(), nullable=False), sa.Column('items', sa.JSON(), nullable=False), sa.Column('status', sa.String(length=16), nullable=False), sa.Column('created_by', sa.String(length=120), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('key', 'version', name='uq_set_key_version') ) op.create_index(op.f('ix_template_sets_key'), 'template_sets', ['key'], unique=False) op.create_table('workflow_templates', sa.Column('id', sa.String(length=32), nullable=False), sa.Column('key', sa.String(length=80), nullable=False), sa.Column('version', sa.Integer(), nullable=False), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('spec', sa.JSON(), nullable=False), sa.Column('status', sa.String(length=16), nullable=False), sa.Column('created_by', sa.String(length=120), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('key', 'version', name='uq_workflow_key_version') ) op.create_index(op.f('ix_workflow_templates_key'), 'workflow_templates', ['key'], unique=False) op.create_table('set_runs', sa.Column('id', sa.String(length=32), nullable=False), sa.Column('punchlist_id', sa.String(length=80), nullable=False), sa.Column('set_template_id', sa.String(length=32), nullable=False), sa.Column('variables', sa.JSON(), nullable=False), sa.Column('created_by', sa.String(length=120), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['punchlist_id'], ['punchlists.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['set_template_id'], ['template_sets.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_table('items', sa.Column('id', sa.String(length=32), nullable=False), sa.Column('punchlist_id', sa.String(length=80), nullable=False), sa.Column('position', sa.Integer(), nullable=False), sa.Column('template_id', sa.String(length=32), nullable=True), sa.Column('spec_inline', sa.JSON(), nullable=True), sa.Column('set_run_id', sa.String(length=32), nullable=True), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('variables', sa.JSON(), nullable=False), sa.Column('current_step_id', sa.String(length=80), nullable=True), sa.Column('status', sa.String(length=24), nullable=False), sa.Column('flag', sa.String(length=24), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False), sa.CheckConstraint('(template_id IS NOT NULL AND spec_inline IS NULL) OR (template_id IS NULL AND spec_inline IS NOT NULL)', name='ck_item_spec_source'), sa.ForeignKeyConstraint(['punchlist_id'], ['punchlists.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['set_run_id'], ['set_runs.id'], ), sa.ForeignKeyConstraint(['template_id'], ['workflow_templates.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_items_punchlist_id'), 'items', ['punchlist_id'], unique=False) op.create_index(op.f('ix_items_status'), 'items', ['status'], unique=False) op.create_table('item_events', sa.Column('id', sa.String(length=32), nullable=False), sa.Column('item_id', sa.String(length=32), nullable=False), sa.Column('seq', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.Column('actor_username', sa.String(length=120), nullable=False), sa.Column('actor_kind', sa.String(length=12), nullable=False), sa.Column('action_key', sa.String(length=60), nullable=False), sa.Column('step_id', sa.String(length=80), nullable=False), sa.Column('from_step', sa.String(length=80), nullable=False), sa.Column('to_step', sa.String(length=80), nullable=True), sa.Column('target', sa.String(length=200), nullable=True), sa.Column('field_values', sa.JSON(), nullable=True), sa.Column('message', sa.Text(), nullable=True), sa.ForeignKeyConstraint(['item_id'], ['items.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('item_id', 'seq', name='uq_item_event_seq') ) op.create_index(op.f('ix_item_events_item_id'), 'item_events', ['item_id'], unique=False) # ### end Alembic commands ### # ── bw-auth accounts kit: seed levels + hard-won warnings (PACKS.md) ── # # WARNING (a): NEVER auto-write a per-instance grant below a member's # app-wide level. A per-instance grant BEATS the app-wide level, so # seeding a DEFAULT grant for an existing all_instances member silently # DEMOTES them on that instance. Only ever write a grant to scope # someone DOWN deliberately. # # WARNING (b): seed levels APPEND-ONLY. Insert a level only if absent; # never DELETE or rewrite one a live app may already be assigning. import json from app.bw_config import SEED_LEVELS bind = op.get_bind() for name, spec in SEED_LEVELS.items(): exists = bind.execute( sa.text("SELECT 1 FROM bw_levels WHERE name = :n"), {"n": name}).first() if not exists: # append-only bind.execute( sa.text("INSERT INTO bw_levels (name, permissions, assignable) " "VALUES (:n, :p, :a)"), {"n": name, "p": json.dumps(list(spec.get("permissions", []))), "a": json.dumps(list(spec.get("assignable", [])))}) # ── published workflow specs are immutable (plan §6): DB-level guard. ── # Postgres only — the code guard in services covers SQLite (tests); this # trigger is the backstop against any future write path that forgets. if bind.dialect.name == "postgresql": op.execute(""" CREATE OR REPLACE FUNCTION forbid_published_workflow_update() RETURNS trigger AS $$ BEGIN IF OLD.status = 'published' AND ( NEW.spec IS DISTINCT FROM OLD.spec OR NEW.key IS DISTINCT FROM OLD.key OR NEW.version IS DISTINCT FROM OLD.version ) THEN RAISE EXCEPTION 'published workflow specs are immutable; publish a new version'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; """) op.execute(""" CREATE TRIGGER trg_workflow_immutable BEFORE UPDATE ON workflow_templates FOR EACH ROW EXECUTE FUNCTION forbid_published_workflow_update(); """) def downgrade() -> None: """Downgrade schema.""" # ### commands auto generated by Alembic - please adjust! ### op.drop_index(op.f('ix_item_events_item_id'), table_name='item_events') op.drop_table('item_events') op.drop_index(op.f('ix_items_status'), table_name='items') op.drop_index(op.f('ix_items_punchlist_id'), table_name='items') op.drop_table('items') op.drop_table('set_runs') op.drop_index(op.f('ix_workflow_templates_key'), table_name='workflow_templates') op.drop_table('workflow_templates') op.drop_index(op.f('ix_template_sets_key'), table_name='template_sets') op.drop_table('template_sets') op.drop_index(op.f('ix_punchlists_external_ref'), table_name='punchlists') op.drop_table('punchlists') op.drop_table('punchlist_seen') op.drop_table('bw_members') op.drop_table('bw_levels') op.drop_table('bw_instances') op.drop_table('bw_grants') # ### end Alembic commands ### bind = op.get_bind() if bind.dialect.name == "postgresql": op.execute("DROP TRIGGER IF EXISTS trg_workflow_immutable ON workflow_templates") op.execute("DROP FUNCTION IF EXISTS forbid_published_workflow_update()")