bvandeusenandClaude Opus 5.5
8c9bb000fa
0121 keeps the ARM_BORN date beside the constant it justifies. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
289 lines
14 KiB
Python
289 lines
14 KiB
Python
"""family canon — platforms, family ideas, the adoption ledger and its decision
|
|
log
|
|
|
|
Revision ID: 0120
|
|
Revises: 0119
|
|
Create Date: 2026-10-06
|
|
|
|
When one project solves something every project on the same platform will
|
|
meet, that solution becomes FAMILY CANON, and every other project on the
|
|
platform answers it: adopted, a variant (with a reason), exempt (with a
|
|
reason), or owed. These tables hold that.
|
|
|
|
- `platforms` — a GLOBAL catalog, the canonical_systems shape: no owner, slug
|
|
is the match key. Seeded with generic technology names only. Nothing here
|
|
names an app, a repo or a house convention: the catalog ships to every
|
|
install.
|
|
- `project_platforms` — which platforms a project is (declared / detected /
|
|
rejected). Membership is what makes "is this in family?" a lookup.
|
|
- `family_ideas` — a NOTE's family state. No new record type: any notes row
|
|
(a note, a snippet, a lesson) becomes an idea by gaining one of these.
|
|
- `family_idea_platforms` — the platforms an idea is for; the only scope
|
|
source. A linked rule topic takes its scope from the idea.
|
|
- `family_idea_references` — reference implementations (snippets).
|
|
- `family_adoptions` — one row per (project, idea): the project's answer.
|
|
- `family_decisions` — append-only log of every promotion and ledger change.
|
|
|
|
No backfill. No project has declared a platform and no note is an idea yet;
|
|
inventing either would assert a judgment nobody made. The seed rows are
|
|
written here verbatim rather than imported, so the migration keeps running
|
|
unchanged after any service-side list moves on.
|
|
"""
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects import postgresql
|
|
from alembic import op
|
|
|
|
revision = "0120"
|
|
down_revision = "0119"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
# One place per whitelist, so each CHECK and the model's tuple cannot drift
|
|
# (a new value later means DROP + ADD CONSTRAINT in one migration).
|
|
_MEMBERSHIP = ("declared", "detected", "rejected")
|
|
_IDEA_STATUSES = ("candidate", "canon", "retired")
|
|
_ADOPTION_STATUSES = ("unassessed", "adopted", "variant", "exempt", "owed")
|
|
_REASONED = ("variant", "exempt")
|
|
_ACTIONS = ("propose", "promote", "revise", "retire", "assess", "undo")
|
|
_DECIDERS = ("agent", "operator", "system")
|
|
|
|
|
|
def _in(column: str, values: tuple[str, ...]) -> str:
|
|
return f"{column} IN (" + ", ".join(f"'{v}'" for v in values) + ")"
|
|
|
|
|
|
# (name, slug, description, markers). A marker with no slash matches a file's
|
|
# basename anywhere in a repo; one with a slash matches the repo-relative
|
|
# path. Empty markers = declare-only, for a platform that leaves no reliable
|
|
# file behind. Generic on purpose — an install adds its own.
|
|
_SEED = (
|
|
("Android app", "android-app",
|
|
"A native Android client: an APK or app bundle installed on phones and tablets.",
|
|
["AndroidManifest.xml"]),
|
|
("iOS app", "ios-app",
|
|
"A native iOS or iPadOS client built with Xcode.",
|
|
["project.pbxproj"]),
|
|
("Web frontend", "web-frontend",
|
|
"A browser client built with a frontend toolchain: single-page apps and server-rendered frontends.",
|
|
["vite.config.*", "svelte.config.*", "vue.config.*", "next.config.*",
|
|
"nuxt.config.*", "angular.json"]),
|
|
("Browser extension", "browser-extension",
|
|
"An extension installed into a web browser and distributed through its add-on store or by file.",
|
|
[]),
|
|
("Desktop app", "desktop-app",
|
|
"A packaged desktop application for Windows, macOS or Linux.",
|
|
["tauri.conf.json", "electron-builder.*"]),
|
|
("Container image", "container-image",
|
|
"Ships as an OCI/Docker image that a host pulls and runs.",
|
|
["Dockerfile", "Containerfile", "*.Dockerfile"]),
|
|
("Go", "go",
|
|
"Written in Go.",
|
|
["go.mod"]),
|
|
("Python", "python",
|
|
"Written in Python.",
|
|
["pyproject.toml", "setup.py", "requirements.txt"]),
|
|
("Rust", "rust",
|
|
"Written in Rust.",
|
|
["Cargo.toml"]),
|
|
("PostgreSQL", "postgresql",
|
|
"Keeps its data in PostgreSQL.",
|
|
[]),
|
|
("GitHub Actions", "github-actions",
|
|
"Verified and released by GitHub Actions workflows.",
|
|
[".github/workflows/*"]),
|
|
("Gitea / Forgejo Actions", "gitea-actions",
|
|
"Verified and released by Gitea or Forgejo Actions workflows.",
|
|
[".gitea/workflows/*", ".forgejo/workflows/*"]),
|
|
)
|
|
|
|
|
|
def upgrade() -> None:
|
|
platforms = op.create_table(
|
|
"platforms",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column("name", sa.Text(), nullable=False),
|
|
sa.Column("slug", sa.Text(), nullable=False),
|
|
sa.Column("description", sa.Text(), nullable=True),
|
|
sa.Column("markers", postgresql.JSONB(), nullable=False,
|
|
server_default=sa.text("'[]'::jsonb")),
|
|
sa.Column("order_index", sa.Integer(), nullable=False, server_default="0"),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("deleted_batch_id", sa.Text(), nullable=True),
|
|
)
|
|
# Unique among LIVE rows only (the canonical_systems convention).
|
|
op.create_index(
|
|
"uq_platforms_slug", "platforms", ["slug"],
|
|
unique=True, postgresql_where=sa.text("deleted_at IS NULL"),
|
|
)
|
|
op.bulk_insert(
|
|
platforms,
|
|
[
|
|
{"name": name, "slug": slug, "description": description,
|
|
"markers": markers, "order_index": index}
|
|
for index, (name, slug, description, markers) in enumerate(_SEED)
|
|
],
|
|
)
|
|
|
|
op.create_table(
|
|
"project_platforms",
|
|
sa.Column("project_id", sa.Integer(),
|
|
sa.ForeignKey("projects.id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("platform_id", sa.Integer(),
|
|
sa.ForeignKey("platforms.id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("state", sa.Text(), nullable=False, server_default="declared"),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_project_platforms_state", "project_platforms", _in("state", _MEMBERSHIP),
|
|
)
|
|
op.create_index("ix_project_platforms_platform_id", "project_platforms", ["platform_id"])
|
|
|
|
op.create_table(
|
|
"family_ideas",
|
|
sa.Column("note_id", sa.Integer(),
|
|
sa.ForeignKey("notes.id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("status", sa.Text(), nullable=False, server_default="candidate"),
|
|
sa.Column("applies_when", sa.Text(), nullable=True),
|
|
sa.Column("canon_version", sa.Integer(), nullable=False, server_default="1"),
|
|
# SET NULL: deleting a topic must not delete the standard it served.
|
|
sa.Column("topic_id", sa.BigInteger(),
|
|
sa.ForeignKey("rulebook_topics.id", ondelete="SET NULL"), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_family_ideas_status", "family_ideas", _in("status", _IDEA_STATUSES),
|
|
)
|
|
# The first promotion criterion, held by the schema: an idea is not canon
|
|
# until it says, in platform terms, when it applies.
|
|
op.create_check_constraint(
|
|
"ck_family_ideas_canon_applies", "family_ideas",
|
|
"status <> 'canon' OR length(btrim(coalesce(applies_when, ''))) > 0",
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_family_ideas_canon_version", "family_ideas", "canon_version >= 1",
|
|
)
|
|
op.create_index(
|
|
"uq_family_ideas_topic", "family_ideas", ["topic_id"],
|
|
unique=True, postgresql_where=sa.text("topic_id IS NOT NULL"),
|
|
)
|
|
op.create_index("ix_family_ideas_status", "family_ideas", ["status"])
|
|
|
|
op.create_table(
|
|
"family_idea_platforms",
|
|
sa.Column("note_id", sa.Integer(),
|
|
sa.ForeignKey("family_ideas.note_id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("platform_id", sa.Integer(),
|
|
sa.ForeignKey("platforms.id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
)
|
|
op.create_index("ix_family_idea_platforms_platform_id", "family_idea_platforms", ["platform_id"])
|
|
|
|
op.create_table(
|
|
"family_idea_references",
|
|
sa.Column("idea_id", sa.Integer(),
|
|
sa.ForeignKey("family_ideas.note_id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("snippet_id", sa.Integer(),
|
|
sa.ForeignKey("notes.id", ondelete="CASCADE"), primary_key=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
)
|
|
op.create_index("ix_family_idea_references_snippet_id", "family_idea_references", ["snippet_id"])
|
|
|
|
op.create_table(
|
|
"family_adoptions",
|
|
sa.Column("id", sa.BigInteger(), primary_key=True),
|
|
sa.Column("project_id", sa.Integer(),
|
|
sa.ForeignKey("projects.id", ondelete="CASCADE"), nullable=False),
|
|
sa.Column("idea_id", sa.Integer(),
|
|
sa.ForeignKey("family_ideas.note_id", ondelete="CASCADE"), nullable=False),
|
|
sa.Column("status", sa.Text(), nullable=False, server_default="unassessed"),
|
|
sa.Column("reason", sa.Text(), nullable=True),
|
|
sa.Column("canon_version", sa.Integer(), nullable=True),
|
|
sa.Column("assessed_at", sa.DateTime(timezone=True), nullable=True),
|
|
sa.Column("decided_via", sa.Text(), nullable=True),
|
|
sa.Column("owed_task_id", sa.Integer(),
|
|
sa.ForeignKey("notes.id", ondelete="SET NULL"), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_family_adoptions_status", "family_adoptions", _in("status", _ADOPTION_STATUSES),
|
|
)
|
|
# A departure is only a record if it says why.
|
|
op.create_check_constraint(
|
|
"ck_family_adoptions_reason", "family_adoptions",
|
|
"NOT (" + _in("status", _REASONED) + ") "
|
|
"OR length(btrim(coalesce(reason, ''))) > 0",
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_family_adoptions_decided_via", "family_adoptions",
|
|
"decided_via IS NULL OR " + _in("decided_via", _DECIDERS),
|
|
)
|
|
op.create_index(
|
|
"uq_family_adoptions_pair", "family_adoptions", ["project_id", "idea_id"], unique=True,
|
|
)
|
|
op.create_index("ix_family_adoptions_project_id", "family_adoptions", ["project_id"])
|
|
op.create_index("ix_family_adoptions_idea_id", "family_adoptions", ["idea_id"])
|
|
|
|
op.create_table(
|
|
"family_decisions",
|
|
sa.Column("id", sa.BigInteger(), primary_key=True),
|
|
sa.Column("idea_id", sa.Integer(),
|
|
sa.ForeignKey("family_ideas.note_id", ondelete="CASCADE"), nullable=False),
|
|
sa.Column("project_id", sa.Integer(),
|
|
sa.ForeignKey("projects.id", ondelete="CASCADE"), nullable=True),
|
|
sa.Column("action", sa.Text(), nullable=False),
|
|
sa.Column("reason", sa.Text(), nullable=False),
|
|
sa.Column("before", postgresql.JSONB(), nullable=True),
|
|
sa.Column("after", postgresql.JSONB(), nullable=True),
|
|
sa.Column("evidence", postgresql.JSONB(), nullable=True),
|
|
sa.Column("precedent_ids", postgresql.JSONB(), nullable=False,
|
|
server_default=sa.text("'[]'::jsonb")),
|
|
sa.Column("decided_via", sa.Text(), nullable=False, server_default="agent"),
|
|
sa.Column("user_id", sa.Integer(),
|
|
sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_family_decisions_action", "family_decisions", _in("action", _ACTIONS),
|
|
)
|
|
op.create_check_constraint(
|
|
"ck_family_decisions_decided_via", "family_decisions", _in("decided_via", _DECIDERS),
|
|
)
|
|
# The reason IS the record — the agent decides with no approval step, so
|
|
# a decision that cannot say why is not one anybody can review or follow.
|
|
op.create_check_constraint(
|
|
"ck_family_decisions_reason", "family_decisions", "length(btrim(reason)) > 0",
|
|
)
|
|
op.create_index("ix_family_decisions_idea_id", "family_decisions", ["idea_id"])
|
|
op.create_index("ix_family_decisions_project_id", "family_decisions", ["project_id"])
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index("ix_family_decisions_project_id", table_name="family_decisions")
|
|
op.drop_index("ix_family_decisions_idea_id", table_name="family_decisions")
|
|
op.drop_table("family_decisions")
|
|
|
|
op.drop_index("ix_family_adoptions_idea_id", table_name="family_adoptions")
|
|
op.drop_index("ix_family_adoptions_project_id", table_name="family_adoptions")
|
|
op.drop_index("uq_family_adoptions_pair", table_name="family_adoptions")
|
|
op.drop_table("family_adoptions")
|
|
|
|
op.drop_index("ix_family_idea_references_snippet_id", table_name="family_idea_references")
|
|
op.drop_table("family_idea_references")
|
|
|
|
op.drop_index("ix_family_idea_platforms_platform_id", table_name="family_idea_platforms")
|
|
op.drop_table("family_idea_platforms")
|
|
|
|
op.drop_index("ix_family_ideas_status", table_name="family_ideas")
|
|
op.drop_index("uq_family_ideas_topic", table_name="family_ideas")
|
|
op.drop_table("family_ideas")
|
|
|
|
op.drop_index("ix_project_platforms_platform_id", table_name="project_platforms")
|
|
op.drop_table("project_platforms")
|
|
|
|
op.drop_index("uq_platforms_slug", table_name="platforms")
|
|
op.drop_table("platforms")
|