"""initial schema: domain + interaction embryo + kit tables + caddie seam Revision ID: 2111c624e7d2 Revises: Create Date: 2026-09-01 12:49:02.713616 """ from typing import Sequence, Union from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision: str = '2111c624e7d2' 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('caddie_links', sa.Column('assignment_id', sa.String(length=64), nullable=False), sa.Column('instance_id', sa.String(length=64), nullable=False), sa.Column('participants', sa.JSON(none_as_null=True), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('assignment_id') ) op.create_index(op.f('ix_caddie_links_instance_id'), 'caddie_links', ['instance_id'], unique=False) op.create_table('events', sa.Column('id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.String(length=64), nullable=False), sa.Column('actor', sa.String(length=64), nullable=False), sa.Column('kind', sa.String(length=50), nullable=False), sa.Column('payload', sa.JSON(none_as_null=True), nullable=True), sa.Column('occurred_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_events_instance_id'), 'events', ['instance_id'], unique=False) op.create_table('notifications', sa.Column('id', sa.Integer(), nullable=False), sa.Column('recipient', sa.String(length=64), nullable=False), sa.Column('actor', sa.String(length=64), nullable=False), sa.Column('app', sa.String(length=40), nullable=False), sa.Column('kind', sa.String(length=20), nullable=False), sa.Column('category', sa.String(length=60), nullable=False), sa.Column('context_label', sa.String(length=120), nullable=False), sa.Column('body', sa.String(length=200), nullable=False), sa.Column('url', sa.String(length=500), nullable=False), sa.Column('source_type', sa.String(length=40), nullable=False), sa.Column('source_id', sa.String(length=64), nullable=False), sa.Column('project_id', sa.String(length=64), nullable=True), sa.Column('dedupe_key', sa.String(length=160), nullable=False), sa.Column('occurred_at', sa.DateTime(timezone=True), nullable=False), sa.Column('read_at', sa.DateTime(timezone=True), nullable=True), sa.Column('delivered_at', sa.DateTime(timezone=True), nullable=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('dedupe_key') ) op.create_index(op.f('ix_notifications_recipient'), 'notifications', ['recipient'], unique=False) op.create_table('project_details', sa.Column('instance_id', sa.String(length=64), nullable=False), sa.Column('description', sa.Text(), nullable=False), sa.Column('client_party_id', sa.Uuid(as_uuid=False), nullable=True), sa.Column('external_ref', sa.String(length=128), nullable=True), sa.Column('sent_at', sa.DateTime(timezone=True), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('instance_id') ) op.create_table('screens', sa.Column('id', sa.Integer(), nullable=False), sa.Column('instance_id', sa.String(length=64), nullable=False), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('slug', sa.String(length=80), nullable=False), sa.Column('position', sa.Integer(), nullable=False), sa.Column('selected_option_id', sa.Integer(), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['selected_option_id'], ['options.id'], name='fk_screens_selected_option', use_alter=True), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('instance_id', 'slug') ) op.create_index(op.f('ix_screens_instance_id'), 'screens', ['instance_id'], unique=False) op.create_table('thread', sa.Column('id', sa.Integer(), nullable=False), sa.Column('project_id', sa.String(length=64), nullable=True), sa.Column('subject_type', sa.String(length=40), nullable=False), sa.Column('subject_id', sa.String(length=64), nullable=False), sa.Column('title', sa.String(length=255), nullable=False), sa.Column('resolved', sa.Boolean(), nullable=False), sa.Column('created_by', sa.String(length=64), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('subject_type', 'subject_id') ) op.create_index(op.f('ix_thread_project_id'), 'thread', ['project_id'], unique=False) op.create_table('comment', sa.Column('id', sa.Integer(), nullable=False), sa.Column('thread_id', sa.Integer(), nullable=False), sa.Column('body_md', sa.Text(), nullable=False), sa.Column('author_username', sa.String(length=64), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.Column('edited_at', sa.DateTime(timezone=True), nullable=True), sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint(['thread_id'], ['thread.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_comment_thread_id'), 'comment', ['thread_id'], unique=False) op.create_table('options', sa.Column('id', sa.Integer(), nullable=False), sa.Column('screen_id', sa.Integer(), nullable=False), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('concept_tag', sa.String(length=120), nullable=False), sa.Column('position', sa.Integer(), nullable=False), sa.Column('entry_path', sa.String(length=255), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['screen_id'], ['screens.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_options_screen_id'), 'options', ['screen_id'], unique=False) op.create_table('attachment', sa.Column('id', sa.Integer(), nullable=False), sa.Column('comment_id', sa.Integer(), nullable=False), sa.Column('stored_name', sa.String(length=64), nullable=False), sa.Column('original_name', sa.String(length=255), nullable=False), sa.Column('content_type', sa.String(length=100), nullable=False), sa.Column('bytes', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['comment_id'], ['comment.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_attachment_comment_id'), 'attachment', ['comment_id'], unique=False) op.create_table('mockup_files', sa.Column('id', sa.String(length=32), nullable=False), sa.Column('option_id', sa.Integer(), nullable=False), sa.Column('rel_path', sa.String(length=255), nullable=False), sa.Column('stored_name', sa.String(length=64), nullable=False), sa.Column('content_type', sa.String(length=100), nullable=False), sa.Column('bytes', sa.Integer(), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['option_id'], ['options.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('option_id', 'rel_path') ) op.create_index(op.f('ix_mockup_files_option_id'), 'mockup_files', ['option_id'], unique=False) op.create_table('pins', sa.Column('id', sa.Integer(), nullable=False), sa.Column('option_id', sa.Integer(), nullable=False), sa.Column('thread_id', sa.Integer(), nullable=False), sa.Column('x_percent', sa.Float(), nullable=False), sa.Column('y_percent', sa.Float(), nullable=False), sa.Column('locked', sa.Boolean(), nullable=False), sa.Column('created_by', sa.String(length=64), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['option_id'], ['options.id'], ), sa.ForeignKeyConstraint(['thread_id'], ['thread.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('thread_id') ) op.create_index(op.f('ix_pins_option_id'), 'pins', ['option_id'], unique=False) op.create_table('walkthrough_steps', sa.Column('id', sa.Integer(), nullable=False), sa.Column('option_id', sa.Integer(), nullable=False), sa.Column('step_order', sa.Integer(), nullable=False), sa.Column('title', sa.String(length=200), nullable=False), sa.Column('body_md', sa.Text(), nullable=False), sa.Column('target_selector', sa.String(length=255), nullable=False), sa.Column('rect', sa.JSON(none_as_null=True), nullable=True), sa.Column('requires_approval', sa.Boolean(), nullable=False), sa.Column('created_by', sa.String(length=64), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['option_id'], ['options.id'], ), sa.PrimaryKeyConstraint('id') ) op.create_index(op.f('ix_walkthrough_steps_option_id'), 'walkthrough_steps', ['option_id'], unique=False) op.create_table('step_approvals', sa.Column('id', sa.Integer(), nullable=False), sa.Column('step_id', sa.Integer(), nullable=False), sa.Column('username', sa.String(length=64), nullable=False), sa.Column('status', sa.String(length=30), nullable=False), sa.Column('comment', sa.Text(), nullable=True), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), sa.ForeignKeyConstraint(['step_id'], ['walkthrough_steps.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('step_id', 'username') ) op.create_index(op.f('ix_step_approvals_step_id'), 'step_approvals', ['step_id'], unique=False) # ### end Alembic commands ### # ------------------------------------------------------------------ seeds # Level seeds, APPEND-ONLY (PACKS.md warning (b): never DELETE/rewrite a # level a live app may be assigning). And PACKS.md warning (a): NEVER # auto-write a per-instance grant below a member's app-wide level — a grant # BEATS the app-wide level and silently DEMOTES an all_instances member. 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", [])))}) # Member carried over from the pre-rebuild stdlib store (data/accounts.db): # adi was admin, added by rian 2026-08-28. all_instances=True because the # manager side works across every project — without it, an admin with no # per-instance grant has NO effective level on any instance (kit semantics) # and cannot even see them. Idempotent. if not bind.execute(sa.text("SELECT 1 FROM bw_members WHERE username = 'adi'")).first(): bind.execute(sa.text( "INSERT INTO bw_members (username, level, all_instances, added_by) " "VALUES ('adi', 'admin', :t, 'rian')"), {"t": True}) def downgrade() -> None: """Downgrade schema.""" # ### commands auto generated by Alembic - please adjust! ### op.drop_index(op.f('ix_step_approvals_step_id'), table_name='step_approvals') op.drop_table('step_approvals') op.drop_index(op.f('ix_walkthrough_steps_option_id'), table_name='walkthrough_steps') op.drop_table('walkthrough_steps') op.drop_index(op.f('ix_pins_option_id'), table_name='pins') op.drop_table('pins') op.drop_index(op.f('ix_mockup_files_option_id'), table_name='mockup_files') op.drop_table('mockup_files') op.drop_index(op.f('ix_attachment_comment_id'), table_name='attachment') op.drop_table('attachment') op.drop_index(op.f('ix_options_screen_id'), table_name='options') op.drop_table('options') op.drop_index(op.f('ix_comment_thread_id'), table_name='comment') op.drop_table('comment') op.drop_index(op.f('ix_thread_project_id'), table_name='thread') op.drop_table('thread') op.drop_index(op.f('ix_screens_instance_id'), table_name='screens') op.drop_table('screens') op.drop_table('project_details') op.drop_index(op.f('ix_notifications_recipient'), table_name='notifications') op.drop_table('notifications') op.drop_index(op.f('ix_events_instance_id'), table_name='events') op.drop_table('events') op.drop_index(op.f('ix_caddie_links_instance_id'), table_name='caddie_links') op.drop_table('caddie_links') op.drop_table('bw_members') op.drop_table('bw_levels') op.drop_table('bw_instances') op.drop_table('bw_grants') # ### end Alembic commands ###