From 17ab56a3c7d3dbb3d66edf380ab637a61cefe50c Mon Sep 17 00:00:00 2001 From: Dennis Fink Date: Tue, 27 Oct 2026 23:16:27 +0100 Subject: feat(core): add database CLI Make database initialization and upgrades explicit so new installations cannot silently modify an existing or unversioned schema. Keep Flask-Migrate commands available separately and package the migration environment with the application for reliable deployment. --- webmentions_ssg/__init__.py | 22 ++--- webmentions_ssg/cli/database.py | 49 ++++++++++ webmentions_ssg/cli/mail.py | 4 + webmentions_ssg/cli/users.py | 4 + webmentions_ssg/migrations/README | 1 + webmentions_ssg/migrations/alembic.ini | 54 +++++++++++ webmentions_ssg/migrations/env.py | 105 +++++++++++++++++++++ webmentions_ssg/migrations/script.py.mako | 24 +++++ .../versions/681a2a9a1bc0_webmention_sender.py | 86 +++++++++++++++++ ...a3d_do_not_limit_username_and_password_hash_.py | 58 ++++++++++++ .../f63641044cc1_initial_database_schema.py | 58 ++++++++++++ 11 files changed, 453 insertions(+), 12 deletions(-) create mode 100644 webmentions_ssg/cli/database.py create mode 100644 webmentions_ssg/migrations/README create mode 100644 webmentions_ssg/migrations/alembic.ini create mode 100644 webmentions_ssg/migrations/env.py create mode 100644 webmentions_ssg/migrations/script.py.mako create mode 100644 webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py create mode 100644 webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py create mode 100644 webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py (limited to 'webmentions_ssg') diff --git a/webmentions_ssg/__init__.py b/webmentions_ssg/__init__.py index 3dda31b..7eb8b98 100644 --- a/webmentions_ssg/__init__.py +++ b/webmentions_ssg/__init__.py @@ -9,10 +9,9 @@ from pathlib import Path import coloredlogs from flask import Flask -from flask.cli import AppGroup from flask_bootstrap import Bootstrap5 from flask_mail import Mail -from flask_migrate import Migrate, upgrade +from flask_migrate import Migrate from flask_sqlalchemy import SQLAlchemy from flask_wtf.csrf import CSRFProtect from sqlalchemy.orm import DeclarativeBase @@ -36,8 +35,6 @@ MIGRATE = Migrate() HUEY = Huey() MAIL = Mail() -DB_CLI = AppGroup("db", help="Handle database") - def create_app(config_class: type[DefaultConfig] | None = None) -> Flask: """ @@ -103,7 +100,12 @@ def create_app(config_class: type[DefaultConfig] | None = None) -> Flask: ) DATABASE.init_app(app) - MIGRATE.init_app(app, db=DATABASE) + MIGRATE.init_app( + app, + db=DATABASE, + directory=str(Path(__file__).resolve().parent / "migrations"), + command="migrate", + ) CSRF.init_app(app) MAIL.init_app(app) @@ -134,12 +136,8 @@ def create_app(config_class: type[DefaultConfig] | None = None) -> Flask: app.cli.add_command(MAIL_CLI) - return app + from .cli.database import DB_CLI + app.cli.add_command(DB_CLI) -@DB_CLI.command("create") -def create_db() -> None: - """ - Upgrade the database to the latest migration revision. - """ - upgrade() + return app diff --git a/webmentions_ssg/cli/database.py b/webmentions_ssg/cli/database.py new file mode 100644 index 0000000..e918600 --- /dev/null +++ b/webmentions_ssg/cli/database.py @@ -0,0 +1,49 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Simple database commands built on Flask-Migrate.""" + +import click +from flask.cli import AppGroup +from flask_migrate import upgrade as migrate_upgrade +from sqlalchemy import inspect + +from .. import DATABASE + +DB_CLI = AppGroup("db", help="Handle database") + + +def _table_names() -> set[str]: + """Return existing table names without modifying the database schema.""" + with DATABASE.engine.connect() as connection: + return set(inspect(connection).get_table_names()) + + +@DB_CLI.command("create-db", help="Create a new database using all migrations") +def create_db() -> None: + """Initialize an empty database and record its Alembic revision.""" + if _table_names(): + raise click.ClickException( + "The database already contains tables. " + "Use 'flask db upgrade' for an existing migrated database." + ) + + # Applying migrations (rather than create_all()) keeps alembic_version in sync. + migrate_upgrade() + click.echo("Database created at the latest migration revision.") + + +@DB_CLI.command("upgrade", help="Upgrade an existing database to the latest revision") +def upgrade_db() -> None: + """Apply pending migrations to a previously initialized database.""" + if "alembic_version" not in _table_names(): + raise click.ClickException( + "No Flask-Migrate revision table found. " + "For a new, empty database run 'flask db create-db'. " + "For a legacy database, inspect its schema and use " + "'flask migrate stamp' only if appropriate." + ) + + migrate_upgrade() + click.echo("Database upgraded to the latest migration revision.") diff --git a/webmentions_ssg/cli/mail.py b/webmentions_ssg/cli/mail.py index 07dc50b..3ea903e 100644 --- a/webmentions_ssg/cli/mail.py +++ b/webmentions_ssg/cli/mail.py @@ -1,3 +1,7 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + import click from flask import current_app from flask.cli import AppGroup diff --git a/webmentions_ssg/cli/users.py b/webmentions_ssg/cli/users.py index 2dcb17e..4ece2f4 100644 --- a/webmentions_ssg/cli/users.py +++ b/webmentions_ssg/cli/users.py @@ -1,3 +1,7 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + import click import sqlalchemy as sa from flask.cli import AppGroup diff --git a/webmentions_ssg/migrations/README b/webmentions_ssg/migrations/README new file mode 100644 index 0000000..0e04844 --- /dev/null +++ b/webmentions_ssg/migrations/README @@ -0,0 +1 @@ +Single-database configuration for Flask. diff --git a/webmentions_ssg/migrations/alembic.ini b/webmentions_ssg/migrations/alembic.ini new file mode 100644 index 0000000..bdfa231 --- /dev/null +++ b/webmentions_ssg/migrations/alembic.ini @@ -0,0 +1,54 @@ +; SPDX-FileCopyrightText: 2026 Dennis Fink +; +; SPDX-License-Identifier: BSD-3-Clause + +# A generic, single database configuration. + +[alembic] +# template used to generate migration files +# file_template = %%(rev)s_%%(slug)s + +# set to 'true' to run the environment during +# the 'revision' command, regardless of autogenerate +# revision_environment = false + + +# Logging configuration +[loggers] +keys = root,sqlalchemy,alembic,flask_migrate + +[handlers] +keys = console + +[formatters] +keys = generic + +[logger_root] +level = WARN +handlers = console +qualname = + +[logger_sqlalchemy] +level = WARN +handlers = +qualname = sqlalchemy.engine + +[logger_alembic] +level = INFO +handlers = +qualname = alembic + +[logger_flask_migrate] +level = INFO +handlers = +qualname = flask_migrate + +[handler_console] +class = StreamHandler +args = (sys.stderr,) +level = NOTSET +formatter = generic + +[formatter_generic] +format = %(levelname)-5.5s [%(name)s] %(message)s +datefmt = %H:%M:%S diff --git a/webmentions_ssg/migrations/env.py b/webmentions_ssg/migrations/env.py new file mode 100644 index 0000000..e2e0ec9 --- /dev/null +++ b/webmentions_ssg/migrations/env.py @@ -0,0 +1,105 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +import logging + +from alembic import context +from flask import current_app + +# this is the Alembic Config object, which provides +# access to the values within the .ini file in use. +config = context.config + +# Flask already configures application logging. +# Do not let Alembic replace existing logging handlers, +# including pytest's log capture handler. +logger = logging.getLogger("alembic.env") + + +def get_engine(): + return current_app.extensions["migrate"].db.engine + + +def get_engine_url(): + try: + return get_engine().url.render_as_string(hide_password=False).replace("%", "%%") + except AttributeError: + return str(get_engine().url).replace("%", "%%") + + +# add your model's MetaData object here +# for 'autogenerate' support +# from myapp import mymodel +# target_metadata = mymodel.Base.metadata +config.set_main_option("sqlalchemy.url", get_engine_url()) +target_db = current_app.extensions["migrate"].db + +# other values from the config, defined by the needs of env.py, +# can be acquired: +# my_important_option = config.get_main_option("my_important_option") +# ... etc. + + +def get_metadata(): + if hasattr(target_db, "metadatas"): + return target_db.metadatas[None] + return target_db.metadata + + +def run_migrations_offline(): + """Run migrations in 'offline' mode. + + This configures the context with just a URL + and not an Engine, though an Engine is acceptable + here as well. By skipping the Engine creation + we don't even need a DBAPI to be available. + + Calls to context.execute() here emit the given string to the + script output. + + """ + url = config.get_main_option("sqlalchemy.url") + context.configure(url=url, target_metadata=get_metadata(), literal_binds=True) + + with context.begin_transaction(): + context.run_migrations() + + +def run_migrations_online(): + """Run migrations in 'online' mode. + + In this scenario we need to create an Engine + and associate a connection with the context. + + """ + + # this callback is used to prevent an auto-migration from being generated + # when there are no changes to the schema + # reference: http://alembic.zzzcomputing.com/en/latest/cookbook.html + def process_revision_directives(context, revision, directives): + if getattr(config.cmd_opts, "autogenerate", False): + script = directives[0] + if script.upgrade_ops.is_empty(): + directives[:] = [] + logger.info("No changes in schema detected.") + + conf_args = current_app.extensions["migrate"].configure_args + if conf_args.get("process_revision_directives") is None: + conf_args["process_revision_directives"] = process_revision_directives + + connectable = get_engine() + + with connectable.connect() as connection: + context.configure( + connection=connection, target_metadata=get_metadata(), **conf_args + ) + + with context.begin_transaction(): + context.run_migrations() + + +if context.is_offline_mode(): + run_migrations_offline() +else: + run_migrations_online() diff --git a/webmentions_ssg/migrations/script.py.mako b/webmentions_ssg/migrations/script.py.mako new file mode 100644 index 0000000..2c01563 --- /dev/null +++ b/webmentions_ssg/migrations/script.py.mako @@ -0,0 +1,24 @@ +"""${message} + +Revision ID: ${up_revision} +Revises: ${down_revision | comma,n} +Create Date: ${create_date} + +""" +from alembic import op +import sqlalchemy as sa +${imports if imports else ""} + +# revision identifiers, used by Alembic. +revision = ${repr(up_revision)} +down_revision = ${repr(down_revision)} +branch_labels = ${repr(branch_labels)} +depends_on = ${repr(depends_on)} + + +def upgrade(): + ${upgrades if upgrades else "pass"} + + +def downgrade(): + ${downgrades if downgrades else "pass"} diff --git a/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py b/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py new file mode 100644 index 0000000..23bf2dd --- /dev/null +++ b/webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py @@ -0,0 +1,86 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Webmention sender + +Revision ID: 681a2a9a1bc0 +Revises: f63641044cc1 +Create Date: 2026-08-14 13:40:55.149604 + +""" + +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = "681a2a9a1bc0" +down_revision = "f63641044cc1" +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.create_table( + "sources", + sa.Column("uuid", sa.Uuid(), nullable=False), + sa.Column("path", sa.Text(), nullable=False), + sa.Column("url", sa.Text(), nullable=False), + sa.Column("content_hash", sa.String(length=32), nullable=False), + sa.Column("revision", sa.Integer(), nullable=False), + sa.Column("last_seen_at", sa.DateTime(timezone=True), nullable=False), + sa.Column("revised_at", sa.DateTime(timezone=True), nullable=False), + sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), + sa.PrimaryKeyConstraint("uuid"), + sa.UniqueConstraint("path"), + sa.UniqueConstraint("url"), + ) + op.create_table( + "sent_webmentions", + sa.Column("uuid", sa.Uuid(), nullable=False), + sa.Column("source_id", sa.Uuid(), nullable=False), + sa.Column("target", sa.Text(), nullable=False), + sa.Column("active", sa.Boolean(), nullable=False), + sa.Column("desired_revision", sa.Integer(), nullable=False), + sa.Column("processed_revision", sa.Integer(), nullable=True), + sa.Column("sent_revision", sa.Integer(), nullable=True), + sa.Column( + "status", + sa.Enum( + "sent", + "unsupported", + "failed", + name="sent_webmention_status", + native_enum=False, + ), + nullable=True, + ), + sa.Column("failure_reason", sa.Text(), nullable=True), + sa.Column("endpoint", sa.Text(), nullable=True), + sa.Column("response_status", sa.Integer(), nullable=True), + sa.Column("status_url", sa.Text(), nullable=True), + sa.Column("last_attempted_at", sa.DateTime(timezone=True), nullable=True), + sa.Column("last_sent_at", sa.DateTime(timezone=True), nullable=True), + sa.ForeignKeyConstraint(["source_id"], ["sources.uuid"], ondelete="CASCADE"), + sa.PrimaryKeyConstraint("uuid"), + sa.UniqueConstraint( + "source_id", "target", name="uq_sent_webmention_source_target" + ), + ) + with op.batch_alter_table("sent_webmentions", schema=None) as batch_op: + batch_op.create_index( + batch_op.f("ix_sent_webmentions_source_id"), ["source_id"], unique=False + ) + + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("sent_webmentions", schema=None) as batch_op: + batch_op.drop_index(batch_op.f("ix_sent_webmentions_source_id")) + + op.drop_table("sent_webmentions") + op.drop_table("sources") + # ### end Alembic commands ### diff --git a/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py b/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py new file mode 100644 index 0000000..4bb7a7c --- /dev/null +++ b/webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py @@ -0,0 +1,58 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Do not limit username and password hash length + +Revision ID: be6e229d6a3d +Revises: 681a2a9a1bc0 +Create Date: 2026-08-16 20:38:27.992624 + +""" + +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = "be6e229d6a3d" +down_revision = "681a2a9a1bc0" +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.alter_column( + "username", + existing_type=sa.VARCHAR(length=64), + type_=sa.Text(), + existing_nullable=False, + ) + batch_op.alter_column( + "password_hash", + existing_type=sa.VARCHAR(length=256), + type_=sa.Text(), + nullable=False, + ) + + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.alter_column( + "password_hash", + existing_type=sa.Text(), + type_=sa.VARCHAR(length=256), + nullable=True, + ) + batch_op.alter_column( + "username", + existing_type=sa.Text(), + type_=sa.VARCHAR(length=64), + existing_nullable=False, + ) + + # ### end Alembic commands ### diff --git a/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py b/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py new file mode 100644 index 0000000..5017bc3 --- /dev/null +++ b/webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py @@ -0,0 +1,58 @@ +# SPDX-FileCopyrightText: 2026 Dennis Fink +# +# SPDX-License-Identifier: BSD-3-Clause + +"""Initial database schema + +Revision ID: f63641044cc1 +Revises: +Create Date: 2026-08-09 11:18:10.051888 + +""" + +import sqlalchemy as sa +from alembic import op + +# revision identifiers, used by Alembic. +revision = "f63641044cc1" +down_revision = None +branch_labels = None +depends_on = None + + +def upgrade(): + # ### commands auto generated by Alembic - please adjust! ### + op.create_table( + "received_webmentions", + sa.Column("uuid", sa.Uuid(), nullable=False), + sa.Column("source", sa.Text(), nullable=False), + sa.Column("target", sa.Text(), nullable=False), + sa.Column("status", sa.Text(), nullable=False), + sa.Column("failure_reason", sa.Text(), nullable=True), + sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), + sa.PrimaryKeyConstraint("uuid"), + sa.UniqueConstraint("source", "target", name="uq_webmention_source_target"), + ) + op.create_table( + "users", + sa.Column("id", sa.Integer(), nullable=False), + sa.Column("username", sa.String(length=64), nullable=False), + sa.Column("password_hash", sa.String(length=256), nullable=True), + sa.PrimaryKeyConstraint("id"), + ) + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.create_index( + batch_op.f("ix_users_username"), ["username"], unique=True + ) + + # ### end Alembic commands ### + + +def downgrade(): + # ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table("users", schema=None) as batch_op: + batch_op.drop_index(batch_op.f("ix_users_username")) + + op.drop_table("users") + op.drop_table("received_webmentions") + # ### end Alembic commands ### -- cgit v1.3.1