aboutsummaryrefslogtreecommitdiff
path: root/webmentions_ssg
diff options
context:
space:
mode:
authorDennis Fink2026-10-27 23:16:27 +0100
committerDennis Fink2026-10-27 23:18:45 +0100
commit17ab56a3c7d3dbb3d66edf380ab637a61cefe50c (patch)
tree1626e3e1d402a6614a1cec4c2c6719d301f39bac /webmentions_ssg
parent5afb245d13b91047d68d7b7343245c41b6460953 (diff)
downloadwebmentions-ssg-17ab56a3c7d3dbb3d66edf380ab637a61cefe50c.tar.gz
webmentions-ssg-17ab56a3c7d3dbb3d66edf380ab637a61cefe50c.zip
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.
Diffstat (limited to 'webmentions_ssg')
-rw-r--r--webmentions_ssg/__init__.py22
-rw-r--r--webmentions_ssg/cli/database.py49
-rw-r--r--webmentions_ssg/cli/mail.py4
-rw-r--r--webmentions_ssg/cli/users.py4
-rw-r--r--webmentions_ssg/migrations/README1
-rw-r--r--webmentions_ssg/migrations/alembic.ini54
-rw-r--r--webmentions_ssg/migrations/env.py105
-rw-r--r--webmentions_ssg/migrations/script.py.mako24
-rw-r--r--webmentions_ssg/migrations/versions/681a2a9a1bc0_webmention_sender.py86
-rw-r--r--webmentions_ssg/migrations/versions/be6e229d6a3d_do_not_limit_username_and_password_hash_.py58
-rw-r--r--webmentions_ssg/migrations/versions/f63641044cc1_initial_database_schema.py58
11 files changed, 453 insertions, 12 deletions
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 <me+coding@dennisfink.me>
+#
+# 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 <me+coding@dennisfink.me>
+#
+# 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 <me+coding@dennisfink.me>
+#
+# 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 <me+coding@dennisfink.me>
+;
+; 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 <me+coding@dennisfink.me>
+#
+# 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 <me+coding@dennisfink.me>
+#
+# 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 <me+coding@dennisfink.me>
+#
+# 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 <me+coding@dennisfink.me>
+#
+# 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 ###