agilentics / boiler 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
"""the navigation tree: categories and subcategories

Two tables, both org-scoped, both ordered by a fractional rank so a reorder is
one UPDATE rather than a renumbering of the list.

`subcategories.category_id` is nullable and carries no NOT NULL default on
purpose: null is what "ungrouped" means, so there is no row to seed and nothing
to backfill. An organisation that never makes a category has an empty tree, which
is the correct empty state rather than a special case.

Revision ID: 0003
Revises: 0002
Create Date: 2026-01-03
"""
import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "0003"
down_revision = "0002"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "categories",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("org_id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=60), nullable=False),
        sa.Column("rank", sa.Float(), nullable=True),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(),
                  nullable=False),
        sa.ForeignKeyConstraint(["org_id"], ["organizations.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("org_id", "name", name="uq_category_name_per_org"),
    )
    op.create_index("ix_categories_org_id", "categories", ["org_id"])

    op.create_table(
        "subcategories",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("org_id", sa.Integer(), nullable=False),
        sa.Column("category_id", sa.Integer(), nullable=True),
        sa.Column("name", sa.String(length=60), nullable=False),
        sa.Column("slug", sa.String(length=60), nullable=False),
        sa.Column("nav_rank", sa.Float(), nullable=True),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(),
                  nullable=False),
        sa.ForeignKeyConstraint(["org_id"], ["organizations.id"], ondelete="CASCADE"),
        # SET NULL, not CASCADE: deleting a heading must not delete what was
        # filed under it. app.core.categories.delete_category clears the column
        # explicitly as well, because SQLite enforces no foreign key unless
        # asked - so relying on this alone would behave one way in the test
        # database and another in Postgres.
        sa.ForeignKeyConstraint(["category_id"], ["categories.id"],
                                ondelete="SET NULL"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("org_id", "slug", name="uq_subcategory_slug_per_org"),
    )
    op.create_index("ix_subcategories_org_id", "subcategories", ["org_id"])
    op.create_index("ix_subcategories_category_id", "subcategories", ["category_id"])


def downgrade() -> None:
    op.drop_index("ix_subcategories_category_id", table_name="subcategories")
    op.drop_index("ix_subcategories_org_id", table_name="subcategories")
    op.drop_table("subcategories")
    op.drop_index("ix_categories_org_id", table_name="categories")
    op.drop_table("categories")