Augur db somehow gets patially intialized

[MoralCode]

While debugging an augur instance that was initially started up in WSL (multiple times, with no success) before being run outside WSL

When run outside WSL, the initial migration fails with a message indicating the tables already exist.

Something, somehow, is causing the database to get partially initialized, but somehow not stamped with an alembic version.

This is likely a problem with a) the fact that we are using the migrations as our initialization strategy, and b) the fact that the superlegacy migrations (see #3542) arent really using any standard/defined versioning system to prevent migrations from accidentally being run twice (alembic already accounts for this)

This issue is being filed in case a cause is found for this issue and/or to document that it happened

Stack Trace

augur-1         | INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
augur-1         | INFO  [alembic.runtime.migration] Will assume transactional DDL.
augur-1         | INFO  [alembic.runtime.migration] Running upgrade 0 -> 1, Augur New Changes
augur-1         | Traceback (most recent call last):
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
augur-1         |     self.dialect.do_execute(
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
augur-1         |     cursor.execute(statement, parameters)
augur-1         | psycopg2.errors.DuplicateTable: relation "assigniees-unique" already exists
augur-1         |
augur-1         |
augur-1         | The above exception was the direct cause of the following exception:
augur-1         |
augur-1         | Traceback (most recent call last):
augur-1         |   File "/augur/.venv/bin/alembic", line 10, in <module>
augur-1         |     sys.exit(main())
augur-1         |              ^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/config.py", line 1033, in main
augur-1         |     CommandLine(prog=prog).main(argv=argv)
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/config.py", line 1023, in main
augur-1         |     self.run_cmd(cfg, options)
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/config.py", line 957, in run_cmd
augur-1         |     fn(
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/command.py", line 483, in upgrade
augur-1         |     script.run_env()
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/script/base.py", line 545, in run_env
augur-1         |     util.load_python_file(self.dir, "env.py")
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file
augur-1         |     module = load_module_py(module_id, path)
augur-1         |              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1         |     spec.loader.exec_module(module)  # type: ignore
augur-1         |   File "<frozen importlib._bootstrap_external>", line 940, in exec_module
augur-1         |   File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
augur-1         |   File "/augur/augur/application/schema/alembic/env.py", line 116, in <module>
augur-1         |     run_migrations_online()
augur-1         |   File "/augur/augur/application/schema/alembic/env.py", line 110, in run_migrations_online
augur-1         |     context.run_migrations()
augur-1         |   File "<string>", line 8, in run_migrations
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
augur-1         |     self.get_context().run_migrations(**kw)
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
augur-1         |     step.migration_fn(**kw)
augur-1         |   File "/augur/augur/application/schema/alembic/versions/1_augur_new_changes.py", line 24, in upgrade
augur-1         |     change_tables_to_mimic_current_db_1()
augur-1         |   File "/augur/augur/application/schema/alembic/versions/1_augur_new_changes.py", line 75, in change_tables_to_mimic_current_db_1
augur-1         |     op.create_unique_constraint('assigniees-unique', 'pull_request_assignees', ['pull_request_id', 'pr_assignee_src_id'], schema='augur_data')
augur-1         |   File "<string>", line 8, in create_unique_constraint
augur-1         |   File "<string>", line 3, in create_unique_constraint
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/operations/ops.py", line 498, in create_unique_constraint
augur-1         |     return operations.invoke(op)
augur-1         |            ^^^^^^^^^^^^^^^^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/operations/base.py", line 441, in invoke
augur-1         |     return fn(self, operation)
augur-1         |            ^^^^^^^^^^^^^^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/operations/toimpl.py", line 202, in create_constraint
augur-1         |     operations.impl.add_constraint(
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 404, in add_constraint
augur-1         |     self._exec(schema.AddConstraint(const))
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/alembic/ddl/impl.py", line 246, in _exec
augur-1         |     return conn.execute(construct, params)
augur-1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
augur-1         |     return meth(
augur-1         |            ^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/sql/ddl.py", line 181, in _execute_on_connection
augur-1         |     return connection._execute_ddl(
augur-1         |            ^^^^^^^^^^^^^^^^^^^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1528, in _execute_ddl
augur-1         |     ret = self._execute_context(
augur-1         |           ^^^^^^^^^^^^^^^^^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
augur-1         |     return self._exec_single_context(
augur-1         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
augur-1         |     self._handle_dbapi_exception(
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
augur-1         |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
augur-1         |     self.dialect.do_execute(
augur-1         |   File "/augur/.venv/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
augur-1         |     cursor.execute(statement, parameters)
augur-1         | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "assigniees-unique" already exists
augur-1         |
augur-1         | [SQL: ALTER TABLE augur_data.pull_request_assignees ADD CONSTRAINT "assigniees-unique" UNIQUE (pull_request_id, pr_assignee_src_id)]
augur-1         | (Background on this error at: https://sqlalche.me/e/20/f405)
augur-1         |
augur-1         | Collecting data for config...
augur-1         |
augur-1         | Please provide a valid GitHub API key.
augur-1         | For more information on how to create the key, visit:
augur-1         | https://oss-augur.readthedocs.io/en/main/getting-started/installation.html#backend
augur-1         | ** This is required for Augur to gather data ***
augur-1         | You entered a blank line, are you sure?
augur-1         | You entered a blank line, are you sure?
augur-1         | You entered a blank line, are you sure?
augur-1         | You entered a blank line, are you sure?

Slack ref: https://chaoss-workspace.slack.com/archives/C0226ELG6R4/p1768070126330399?thread_ts=1768028726.729149&cid=C0226ELG6R4

[ANJAN672]

Root Cause Analysis & Fix

The Problem

The docker-compose.yml uses image: postgres:16 (vanilla PostgreSQL) which starts with an empty database. When Augur boots, Alembic migrations fail because:

1. Migration 0 → 1 expects existing tables to modify (drop/alter columns)
2. But the vanilla PostgreSQL has no schema loaded
3. Result: relation "contributor_repo" does not exist

Secondary Effect: On subsequent restarts, you get relation "assigniees-unique" already exists because:

• Some constraints were created before the failure
• Alembic's version tracker isn't updated until migration completes
• Next boot tries to create the same constraints again

The Fix

There's already a docker/database/Dockerfile that copies the full schema (augur-new-schema.sql) to PostgreSQL's init directory. The docker-compose.yml just needs to use it:

services:
  augur-db:
-    image: postgres:16
+    build:
+      context: .
+      dockerfile: ./docker/database/Dockerfile
    restart: unless-stopped

[MoralCode]

Looks like this was essentially how the dockerfile worked 4 years ago as set up by derek: b736cde

Then it was removed as part of a WIP commit related to alembic also by derek: 9ac0876

then @sgoggins bumped from postgres 14 to 16 in 22c6848

considering this has been around for 4 years, im a little concerned that we havent noticed this until now, especially given how many people have been using docker for installs in the past ~6 months

[MoralCode]

possble workaround 1: a PR that @ANJAN672 is working on filing

Possible workaround/experiment 2: git checkout d704670fc80db2397e6f20912ad89f6376aaf372 and rebuild the image

• this is the version a couple commits before Augur auto migrations #3416 was merged (i.e. the last time alembic config was changed since docker became mostly viable as a way to install docker