initial commit

3 files changed, 224 insertions, 0 deletions

diff --git a/alembic/env.py b/alembic/env.py
new file mode 100644
index 0000000..6fcbeb0
--- /dev/null
+++ b/alembic/env.py
@@ -0,0 +1,58 @@
+from __future__ import annotations
+
+from logging.config import fileConfig
+
+from alembic import context
+from sqlalchemy import engine_from_config, pool
+
+from srht_contrib.config import Settings
+from srht_contrib.db import Base
+from srht_contrib.models import ActorAlias, ContributionEvent, SyncState, TrackedRepository  # noqa: F401
+
+config = context.config
+
+if config.config_file_name is not None:
+    fileConfig(config.config_file_name)
+
+target_metadata = Base.metadata
+
+
+def get_database_url() -> str:
+    configured_url = config.get_main_option("sqlalchemy.url")
+    if configured_url:
+        return configured_url
+    return Settings().database_url
+
+
+def run_migrations_offline() -> None:
+    context.configure(
+        url=get_database_url(),
+        target_metadata=target_metadata,
+        literal_binds=True,
+        dialect_opts={"paramstyle": "named"},
+    )
+
+    with context.begin_transaction():
+        context.run_migrations()
+
+
+def run_migrations_online() -> None:
+    section = config.get_section(config.config_ini_section) or {}
+    section["sqlalchemy.url"] = get_database_url()
+    connectable = engine_from_config(
+        section,
+        prefix="sqlalchemy.",
+        poolclass=pool.NullPool,
+    )
+
+    with connectable.connect() as connection:
+        context.configure(connection=connection, target_metadata=target_metadata)
+
+        with context.begin_transaction():
+            context.run_migrations()
+
+
+if context.is_offline_mode():
+    run_migrations_offline()
+else:
+    run_migrations_online()
diff --git a/alembic/script.py.mako b/alembic/script.py.mako
new file mode 100644
index 0000000..40c6b2b
--- /dev/null
+++ b/alembic/script.py.mako
@@ -0,0 +1,18 @@
+"""${message}"""
+
+revision = ${repr(up_revision)}
+down_revision = ${repr(down_revision)}
+branch_labels = ${repr(branch_labels)}
+depends_on = ${repr(depends_on)}
+
+from alembic import op
+import sqlalchemy as sa
+${imports if imports else ""}
+
+
+def upgrade() -> None:
+    ${upgrades if upgrades else "pass"}
+
+
+def downgrade() -> None:
+    ${downgrades if downgrades else "pass"}
diff --git a/alembic/versions/20260409_0001_initial_schema.py b/alembic/versions/20260409_0001_initial_schema.py
new file mode 100644
index 0000000..dffbe61
--- /dev/null
+++ b/alembic/versions/20260409_0001_initial_schema.py
@@ -0,0 +1,148 @@
+"""initial schema"""
+
+from __future__ import annotations
+
+from alembic import op
+import sqlalchemy as sa
+from sqlalchemy import inspect
+
+from srht_contrib.config import Settings
+
+
+revision = "20260409_0001"
+down_revision = None
+branch_labels = None
+depends_on = None
+
+
+def _table_names() -> set[str]:
+    return set(inspect(op.get_bind()).get_table_names())
+
+
+def _index_names(table_name: str) -> set[str]:
+    return {index["name"] for index in inspect(op.get_bind()).get_indexes(table_name)}
+
+
+def _tracked_repositories_needs_upgrade() -> bool:
+    inspector = inspect(op.get_bind())
+    columns = {column["name"]: column for column in inspector.get_columns("tracked_repositories")}
+    actor_column = columns.get("actor")
+    if actor_column is None or actor_column.get("nullable", True):
+        return True
+
+    unique_constraints = {constraint["name"] for constraint in inspector.get_unique_constraints("tracked_repositories")}
+    return "uq_tracked_repository_service_actor_name" not in unique_constraints
+
+
+def _upgrade_tracked_repositories() -> None:
+    default_actor = Settings().default_actor
+    op.execute(
+        sa.text(
+            """
+            CREATE TABLE tracked_repositories__alembic_new (
+                id INTEGER NOT NULL PRIMARY KEY,
+                service VARCHAR(32) NOT NULL,
+                repo_name VARCHAR(255) NOT NULL,
+                actor VARCHAR(255) NOT NULL,
+                CONSTRAINT uq_tracked_repository_service_actor_name UNIQUE (service, actor, repo_name)
+            )
+            """
+        )
+    )
+    op.execute(
+        sa.text(
+            """
+            INSERT INTO tracked_repositories__alembic_new (id, service, repo_name, actor)
+            SELECT id, service, repo_name, COALESCE(actor, :default_actor)
+            FROM tracked_repositories
+            """
+        ).bindparams(default_actor=default_actor)
+    )
+    op.execute(sa.text("DROP TABLE tracked_repositories"))
+    op.execute(sa.text("ALTER TABLE tracked_repositories__alembic_new RENAME TO tracked_repositories"))
+
+
+def upgrade() -> None:
+    table_names = _table_names()
+
+    if "contribution_events" not in table_names:
+        op.create_table(
+            "contribution_events",
+            sa.Column("id", sa.Integer(), primary_key=True),
+            sa.Column("service", sa.String(length=32), nullable=False),
+            sa.Column("event_type", sa.String(length=64), nullable=False),
+            sa.Column("actor", sa.String(length=255), nullable=False),
+            sa.Column("repo_name", sa.String(length=255), nullable=True),
+            sa.Column("resource_id", sa.String(length=255), nullable=False),
+            sa.Column("external_uid", sa.String(length=255), nullable=False),
+            sa.Column("occurred_at", sa.DateTime(timezone=True), nullable=False),
+            sa.Column("weight", sa.Float(), nullable=False),
+            sa.Column("raw_payload_json", sa.JSON(), nullable=True),
+            sa.UniqueConstraint("service", "external_uid", name="uq_contribution_event_service_uid"),
+        )
+
+    contribution_event_indexes = _index_names("contribution_events")
+    if "ix_contribution_events_actor_occurred_at" not in contribution_event_indexes:
+        op.create_index(
+            "ix_contribution_events_actor_occurred_at",
+            "contribution_events",
+            ["actor", "occurred_at"],
+            unique=False,
+        )
+    if "ix_contribution_events_service_occurred_at" not in contribution_event_indexes:
+        op.create_index(
+            "ix_contribution_events_service_occurred_at",
+            "contribution_events",
+            ["service", "occurred_at"],
+            unique=False,
+        )
+
+    if "sync_states" not in table_names:
+        op.create_table(
+            "sync_states",
+            sa.Column("id", sa.Integer(), primary_key=True),
+            sa.Column("service", sa.String(length=32), nullable=False),
+            sa.Column("actor", sa.String(length=255), nullable=False),
+            sa.Column("cursor_value", sa.Text(), nullable=True),
+            sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
+            sa.UniqueConstraint("service", "actor", name="uq_sync_state_service_actor"),
+        )
+
+    if "tracked_repositories" not in table_names:
+        op.create_table(
+            "tracked_repositories",
+            sa.Column("id", sa.Integer(), primary_key=True),
+            sa.Column("service", sa.String(length=32), nullable=False),
+            sa.Column("repo_name", sa.String(length=255), nullable=False),
+            sa.Column("actor", sa.String(length=255), nullable=False),
+            sa.UniqueConstraint("service", "actor", "repo_name", name="uq_tracked_repository_service_actor_name"),
+        )
+    elif _tracked_repositories_needs_upgrade():
+        _upgrade_tracked_repositories()
+
+    if "actor_aliases" not in table_names:
+        op.create_table(
+            "actor_aliases",
+            sa.Column("id", sa.Integer(), primary_key=True),
+            sa.Column("canonical_actor", sa.String(length=255), nullable=False),
+            sa.Column("alias", sa.String(length=255), nullable=False),
+            sa.UniqueConstraint("alias", name="uq_actor_alias_alias"),
+        )
+
+
+def downgrade() -> None:
+    table_names = _table_names()
+
+    if "actor_aliases" in table_names:
+        op.drop_table("actor_aliases")
+    if "tracked_repositories" in table_names:
+        op.drop_table("tracked_repositories")
+    if "sync_states" in table_names:
+        op.drop_table("sync_states")
+    if "contribution_events" in table_names:
+        contribution_event_indexes = _index_names("contribution_events")
+        if "ix_contribution_events_service_occurred_at" in contribution_event_indexes:
+            op.drop_index("ix_contribution_events_service_occurred_at", table_name="contribution_events")
+        if "ix_contribution_events_actor_occurred_at" in contribution_event_indexes:
+            op.drop_index("ix_contribution_events_actor_occurred_at", table_name="contribution_events")
+        op.drop_table("contribution_events")