cleveragents-core/alembic/versions/b0_001_projects.py

"""Add ns_projects and project_resource_links tables.

This migration creates the two project-related tables:

- ``ns_projects``: Spec-aligned project definitions identified solely by
  their namespaced name (e.g., ``local/my-project``).  Stores invariants,
  automation profile, invariant actor, context policy, and tags as JSON
  text fields.
- ``project_resource_links``: Many-to-many links between projects and
  resources with optional alias and read-only override.

Revision ID: b0_001_projects
Revises: a5_005_rebaseline_plan_phases
Create Date: 2026-02-15 12:00:00

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "b0_001_projects"
down_revision: str | Sequence[str] | None = "a5_005_rebaseline_plan_phases"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Create ns_projects and project_resource_links tables."""
    # --- ns_projects table ---
    op.create_table(
        "ns_projects",
        # PK: namespaced name (e.g., "local/my-project")
        sa.Column("namespaced_name", sa.String(255), nullable=False),
        # Extracted namespace for filtering (e.g., "local")
        sa.Column("namespace", sa.String(100), nullable=False),
        # Human-readable description
        sa.Column("description", sa.Text(), nullable=True),
        # JSON list of invariant strings
        sa.Column("invariants_json", sa.Text(), nullable=True),
        # Default automation profile name
        sa.Column("automation_profile", sa.String(255), nullable=True),
        # Actor for invariant reconciliation
        sa.Column("invariant_actor", sa.String(255), nullable=True),
        # JSON context policy configuration
        sa.Column("context_policy_json", sa.Text(), nullable=True),
        # JSON array of tag strings
        sa.Column(
            "tags_json",
            sa.Text(),
            nullable=False,
            server_default="[]",
        ),
        # Creator identifier
        sa.Column("created_by", sa.String(255), nullable=True),
        # Timestamps (ISO-8601 strings)
        sa.Column("created_at", sa.String(30), nullable=False),
        sa.Column("updated_at", sa.String(30), nullable=False),
        # Constraints
        sa.PrimaryKeyConstraint("namespaced_name"),
    )

    op.create_index(
        "ix_ns_projects_namespace",
        "ns_projects",
        ["namespace"],
        unique=False,
    )

    # --- project_resource_links table ---
    op.create_table(
        "project_resource_links",
        # PK: ULID (26-char string)
        sa.Column("link_id", sa.String(26), nullable=False),
        # FK to ns_projects
        sa.Column(
            "project_name",
            sa.String(255),
            sa.ForeignKey(
                "ns_projects.namespaced_name",
                ondelete="CASCADE",
                name="fk_project_resource_links_project",
            ),
            nullable=False,
        ),
        # FK to resources
        sa.Column(
            "resource_id",
            sa.String(26),
            sa.ForeignKey(
                "resources.resource_id",
                ondelete="RESTRICT",
                name="fk_project_resource_links_resource",
            ),
            nullable=False,
        ),
        # Optional short name for the resource within the project
        sa.Column("alias", sa.String(255), nullable=True),
        # Read-only override
        sa.Column(
            "read_only",
            sa.Boolean(),
            nullable=False,
            server_default=sa.text("0"),
        ),
        # Timestamp (ISO-8601 string)
        sa.Column("created_at", sa.String(30), nullable=False),
        # Constraints
        sa.PrimaryKeyConstraint("link_id"),
        sa.UniqueConstraint(
            "project_name",
            "resource_id",
            name="uq_project_resource_links_project_resource",
        ),
    )

    op.create_index(
        "ix_project_resource_links_project_name",
        "project_resource_links",
        ["project_name"],
        unique=False,
    )
    op.create_index(
        "ix_project_resource_links_resource_id",
        "project_resource_links",
        ["resource_id"],
        unique=False,
    )
    op.create_index(
        "ix_project_resource_links_project_alias",
        "project_resource_links",
        ["project_name", "alias"],
        unique=False,
    )


def downgrade() -> None:
    """Drop project tables."""
    # Drop in reverse FK order: links -> ns_projects
    op.drop_index(
        "ix_project_resource_links_project_alias",
        table_name="project_resource_links",
    )
    op.drop_index(
        "ix_project_resource_links_resource_id",
        table_name="project_resource_links",
    )
    op.drop_index(
        "ix_project_resource_links_project_name",
        table_name="project_resource_links",
    )
    op.drop_table("project_resource_links")

    op.drop_index("ix_ns_projects_namespace", table_name="ns_projects")
    op.drop_table("ns_projects")