Newer
Older
gnexus-book / server / alembic / versions / ac5f4cd0d7bd_initial_schema.py
"""initial schema

Revision ID: ac5f4cd0d7bd
Revises: 
Create Date: 2026-11-19 18:39:59.501658

"""
from __future__ import annotations

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = 'ac5f4cd0d7bd'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('blobs',
    sa.Column('hash', sa.Text(), nullable=False),
    sa.Column('content', sa.Text(), nullable=False),
    sa.Column('size', sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint('hash')
    )
    op.create_table('documents',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('slug', sa.Text(), nullable=False),
    sa.Column('title', sa.Text(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('slug')
    )
    op.create_table('schemas',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('version', sa.Integer(), nullable=False),
    sa.Column('body', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('name', 'version', name='uq_schemas_name_version')
    )
    op.create_table('users',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('sub', sa.Text(), nullable=False),
    sa.Column('email', sa.Text(), nullable=True),
    sa.Column('display_name', sa.Text(), nullable=True),
    sa.Column('profile', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('locale', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('blocked_at', sa.DateTime(timezone=True), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('sub')
    )
    op.create_table('audit_log',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('actor_user_id', sa.UUID(), nullable=True),
    sa.Column('actor_kind', sa.Text(), nullable=False),
    sa.Column('actor_name', sa.Text(), nullable=True),
    sa.Column('via', sa.Text(), nullable=False),
    sa.Column('action', sa.Text(), nullable=False),
    sa.Column('entity_type', sa.Text(), nullable=False),
    sa.Column('entity_id', sa.Text(), nullable=False),
    sa.Column('before', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('after', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['actor_user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_audit_log_created_at', 'audit_log', ['created_at'], unique=False)
    op.create_table('changesets',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('author_user_id', sa.UUID(), nullable=True),
    sa.Column('summary', sa.Text(), nullable=False),
    sa.Column('reason', sa.Text(), nullable=False),
    sa.Column('status', sa.Text(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('applied_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('validation', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.CheckConstraint("status IN ('draft', 'applied', 'rejected')", name='ck_changesets_status'),
    sa.ForeignKeyConstraint(['author_user_id'], ['users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('inventory_types',
    sa.Column('name', sa.Text(), nullable=False),
    sa.Column('schema_id', sa.UUID(), nullable=True),
    sa.ForeignKeyConstraint(['schema_id'], ['schemas.id'], ),
    sa.PrimaryKeyConstraint('name')
    )
    op.create_table('change_items',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('changeset_id', sa.UUID(), nullable=False),
    sa.Column('kind', sa.Text(), nullable=False),
    sa.Column('target', sa.Text(), nullable=False),
    sa.Column('payload', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('status', sa.Text(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint("kind IN ('doc', 'inventory', 'inventory-item')", name='ck_change_items_kind'),
    sa.CheckConstraint("status IN ('draft', 'applied', 'rejected')", name='ck_change_items_status'),
    sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('doc_versions',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('document_id', sa.UUID(), nullable=False),
    sa.Column('version_no', sa.Integer(), nullable=False),
    sa.Column('parent_version_id', sa.UUID(), nullable=True),
    sa.Column('content_hash', sa.Text(), nullable=False),
    sa.Column('raw', sa.Text(), nullable=False),
    sa.Column('body', sa.Text(), nullable=False),
    sa.Column('frontmatter', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('author_user_id', sa.UUID(), nullable=True),
    sa.Column('actor_kind', sa.Text(), nullable=False),
    sa.Column('actor_name', sa.Text(), nullable=True),
    sa.Column('via', sa.Text(), nullable=False),
    sa.Column('changeset_id', sa.UUID(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['author_user_id'], ['users.id'], ),
    sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ),
    sa.ForeignKeyConstraint(['document_id'], ['documents.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['parent_version_id'], ['doc_versions.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('document_id', 'version_no', name='uq_doc_versions_document_version')
    )
    op.create_index('ix_doc_versions_content_hash', 'doc_versions', ['content_hash'], unique=False)
    op.create_table('inventory_items',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('type', sa.Text(), nullable=False),
    sa.Column('item_id', sa.Text(), nullable=False),
    sa.Column('position', sa.Integer(), nullable=False),
    sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['type'], ['inventory_types.name'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_inventory_items_type_position', 'inventory_items', ['type', 'position'], unique=False)
    op.create_index('uq_inventory_items_type_item_live', 'inventory_items', ['type', 'item_id'], unique=True, postgresql_where=sa.text('deleted_at IS NULL'))
    op.create_table('validation_runs',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('changeset_id', sa.UUID(), nullable=True),
    sa.Column('status', sa.Text(), nullable=False),
    sa.Column('issues', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('inventory_item_versions',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('inventory_item_id', sa.UUID(), nullable=False),
    sa.Column('version_no', sa.Integer(), nullable=False),
    sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('author_user_id', sa.UUID(), nullable=True),
    sa.Column('changeset_id', sa.UUID(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['author_user_id'], ['users.id'], ),
    sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ),
    sa.ForeignKeyConstraint(['inventory_item_id'], ['inventory_items.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('inventory_item_id', 'version_no', name='uq_inventory_item_versions_item_version')
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('inventory_item_versions')
    op.drop_table('validation_runs')
    op.drop_index('uq_inventory_items_type_item_live', table_name='inventory_items', postgresql_where=sa.text('deleted_at IS NULL'))
    op.drop_index('ix_inventory_items_type_position', table_name='inventory_items')
    op.drop_table('inventory_items')
    op.drop_index('ix_doc_versions_content_hash', table_name='doc_versions')
    op.drop_table('doc_versions')
    op.drop_table('change_items')
    op.drop_table('inventory_types')
    op.drop_table('changesets')
    op.drop_index('ix_audit_log_created_at', table_name='audit_log')
    op.drop_table('audit_log')
    op.drop_table('users')
    op.drop_table('schemas')
    op.drop_table('documents')
    op.drop_table('blobs')
    # ### end Alembic commands ###