Introduction

In the previous article, we implemented the complete SQLAlchemy model layer for the AI Tender Opportunity Scanner.

The application now contains models for:

• Users
• Business profiles
• Tender sources
• Ingestion runs
• Raw tender records
• Tenders
• Tender summaries
• Tender embeddings
• Business profile embeddings
• Tender matches
• Saved tenders
• Dismissed tenders
• Processing errors

These models define the structure of the database, but no database tables exist yet.

The next step is creating the initial PostgreSQL schema using Alembic.

Alembic is SQLAlchemy’s migration framework. It allows us to:

• Version database changes
• Create repeatable schema updates
• Track database history
• Apply upgrades safely
• Roll back problematic changes
• Synchronize development and production databases

Without migrations, schema changes quickly become difficult to manage as the application grows.

In this article we will configure Alembic, generate the first migration, enable pgvector support, create all MVP tables, and apply the schema to PostgreSQL.

Development Progress

Project Scope and Roadmap        ████████████████████ 100%
MVP Definition                   ████████████████████ 100%
Technology Stack                 ████████████████████ 100%
System Architecture              ████████████████████ 100%
Database Design                  ████████████████████ 100%
Backend Project Structure        ████████████████████ 100%
SQLAlchemy Models                ████████████████████ 100%
Alembic Migration                ████████████████████ 100%
Repository Layer                 ░░░░░░░░░░░░░░░░░░░░   0%
CRUD Services                    ░░░░░░░░░░░░░░░░░░░░   0%
Tender Ingestion                 ░░░░░░░░░░░░░░░░░░░░   0%
AI Analysis                      ░░░░░░░░░░░░░░░░░░░░   0%
Matching Engine                  ░░░░░░░░░░░░░░░░░░░░   0%
Frontend Dashboard               ░░░░░░░░░░░░░░░░░░░░   0%

What We Are Building

At the end of this article we will have:

• PostgreSQL running locally
• Alembic configured
• pgvector enabled
• SQLAlchemy metadata connected to Alembic
• Initial migration generated
• Database tables created
• Constraints created
• Indexes created
• Upgrade workflow working
• Downgrade workflow working
• Database version tracking enabled

Why Use Migrations?

A common beginner mistake is manually creating tables.

For example:

CREATE TABLE users (...);
CREATE TABLE tenders (...);
CREATE TABLE tender_matches (...);

This quickly becomes difficult to maintain because:

• Team members may have different schemas
• Production and development drift apart
• Rollbacks become difficult
• Changes are undocumented
• Deployment automation becomes harder

Alembic solves these problems by treating database changes as versioned code.

Understanding the Migration Workflow

The workflow is:

SQLAlchemy Models
        |
        v
Alembic Autogenerate
        |
        v
Migration Script
        |
        v
Review Migration
        |
        v
Apply Migration
        |
        v
Database Schema

The migration file becomes the historical record of database evolution.

Step 1: Verify PostgreSQL

Before creating migrations, confirm PostgreSQL is running.

Test connectivity:

psql -U postgres

or

psql -h localhost -U postgres

If successful:

postgres=#

appears.

Exit:

\q

Step 2: Create the Database

Create the database used by the project.

Connect to PostgreSQL:

psql -U postgres

Create the database:

CREATE DATABASE tenderscout;

Verify:

\l

You should see:

tenderscout

Exit:

\q

Step 3: Verify Connection String

Open:

.env

Verify:

DATABASE_URL=postgresql+psycopg://postgres:postgres@localhost:5432/tenderscout

Adjust username, password, host, or port if necessary.

Step 4: Initialize Alembic

From the backend directory:

alembic init alembic

Alembic creates:

alembic/
├── env.py
├── script.py.mako
├── versions/
└── README
alembic.ini

The versions directory will contain migration files.

Understanding the Alembic Structure

alembic.ini

Global Alembic configuration.

env.py

Connects Alembic to the application.

This is the most important configuration file.

versions/

Contains migration history.

Example:

20260718_01_initial_schema.py
20260719_01_add_notifications.py
20260720_01_add_authentication.py

Step 5: Configure Alembic Environment

Open:

alembic/env.py

Replace the default contents with:

from logging.config import fileConfig
from alembic import context
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from app.core.config import settings
from app.database.base import Base
from app.database.base import import_models
config = context.config
config.set_main_option(
    "sqlalchemy.url",
    settings.database_url,
)
if config.config_file_name is not None:
    fileConfig(config.config_file_name)
import_models()
target_metadata = Base.metadata
def run_migrations_offline():
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        compare_type=True,
    )
    with context.begin_transaction():
        context.run_migrations()
def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(
            config.config_ini_section
        ),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
        )
        with context.begin_transaction():
            context.run_migrations()
if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Why Import the Models?

Alembic compares:

Base.metadata

against the current database.

If the models are not imported:

import_models()

Alembic sees:

0 tables

and generates an empty migration.

This is one of the most common mistakes when starting with Alembic.

Why Use compare_type=True?

This tells Alembic to detect type changes.

Example:

String(100)

becomes:

String(255)

Alembic can then generate an appropriate migration.

Without this option, some schema changes may not be detected.

Step 6: Update Alembic Configuration

Open:

alembic.ini

Find:

sqlalchemy.url = driver://user:pass@localhost/dbname

Replace with:

sqlalchemy.url =

Alembic will now load the URL from:

settings.database_url

instead.

This prevents duplication.

Step 7: Verify Metadata Detection

Run:

alembic revision --autogenerate -m "metadata test"

Alembic should detect:

users
business_profiles
tender_sources
ingestion_runs
raw_tender_records
tenders
tender_summaries
tender_embeddings
business_profile_embeddings
tender_matches
saved_tenders
dismissed_tenders
processing_errors

Delete this temporary migration afterward.

It is only a validation step.

Step 8: Enable pgvector

The embedding models use:

VECTOR(...)

Before creating vector columns PostgreSQL must enable the extension.

The migration should contain:

CREATE EXTENSION IF NOT EXISTS vector;

We will add this manually.

Why pgvector Requires an Extension

PostgreSQL does not natively understand:

VECTOR(1536)

until the extension is installed.

Without it, migrations fail with:

type "vector" does not exist

Step 9: Generate the Initial Migration

Generate the real migration:

alembic revision --autogenerate -m "initial schema"

Alembic creates:

alembic/versions/
xxxxxxxx_initial_schema.py

Open the file.

Understanding the Migration Structure

A migration contains two functions:

def upgrade():

Applies changes.

And:

def downgrade():

Reverses changes.

Example:

def upgrade():
    op.create_table(...)
def downgrade():
    op.drop_table(...)

Every migration must support both directions whenever practical.

Step 10: Add pgvector Extension

At the beginning of:

upgrade()

add:

op.execute(
    "CREATE EXTENSION IF NOT EXISTS vector"
)

This ensures vector support exists before vector tables are created.

Step 11: Review Generated Tables

The migration should contain:

op.create_table("users", ...)
op.create_table("business_profiles", ...)
op.create_table("tender_sources", ...)
op.create_table("ingestion_runs", ...)
op.create_table("raw_tender_records", ...)
op.create_table("tenders", ...)
op.create_table("tender_summaries", ...)
op.create_table("tender_embeddings", ...)
op.create_table("business_profile_embeddings", ...)
op.create_table("tender_matches", ...)
op.create_table("saved_tenders", ...)
op.create_table("dismissed_tenders", ...)
op.create_table("processing_errors", ...)

Verify all expected tables are present.

Step 12: Review Constraints

Check for:

Primary Keys

Example:

sa.PrimaryKeyConstraint("id")

Unique Constraints

Examples:

source_id + external_id
user_id + tender_id
business_profile_id + tender_id

Check Constraints

Examples:

overall_score >= 0
overall_score <= 100

and

minimum_contract_value <= maximum_contract_value

Foreign Keys

Examples:

users -> business_profiles
business_profiles -> tender_matches
tenders -> tender_summaries
tenders -> tender_embeddings

Why Review Generated Migrations?

Autogeneration is helpful.

It is not infallible.

Always inspect:

• Constraints
• Indexes
• Foreign keys
• Column types
• Delete rules

before applying a migration.

Production migrations should never be executed blindly.

Step 13: Apply the Migration

Run:

alembic upgrade head

Expected output resembles:

Running upgrade -> initial schema

Alembic applies:

• Tables
• Constraints
• Indexes
• Extension creation

to PostgreSQL.

Understanding head

alembic upgrade head

means:

Apply all migrations up to the newest version.

Other examples:

alembic upgrade +1

Apply one migration.

alembic downgrade -1

Rollback one migration.

alembic downgrade base

Rollback everything.

Step 14: Verify the Version Table

Connect to PostgreSQL:

psql -U postgres -d tenderscout

List tables:

\dt

You should see:

alembic_version

plus all application tables.

What Is alembic_version?

Alembic stores the current migration version.

Example:

SELECT * FROM alembic_version;

Output:

version_num
----------------------------------
8d0f92a81f92

This tells Alembic which migrations have already been applied.

Step 15: Verify Tables

Inside PostgreSQL:

\d users

Example output:

email
hashed_password
full_name
is_active
is_admin
created_at
updated_at

Verify several tables manually.

Step 16: Verify pgvector

Run:

SELECT * FROM pg_extension;

You should see:

vector

among the installed extensions.

Verify Vector Columns

Check:

\d tender_embeddings

You should see:

embedding vector(1536)

Likewise:

\d business_profile_embeddings

should also show:

vector(1536)

Step 17: Test Downgrade

Never assume downgrade works.

Test:

alembic downgrade base

Expected:

Dropping all application tables

Verify:

\dt

Only:

alembic_version

or no application tables remain.

Why Test Downgrades?

Many teams never test rollback paths.

Then a deployment fails and rollback is impossible.

Even if production rarely performs downgrades, the migration should remain reversible whenever feasible.

Step 18: Reapply the Migration

Restore the schema:

alembic upgrade head

Verify again:

\dt

All tables should return.

Step 19: Create a Migration Verification Script

Create:

scripts/check_database.py

Add:

from sqlalchemy import inspect
from app.database.session import engine
EXPECTED_TABLES = {
    "users",
    "business_profiles",
    "tender_sources",
    "ingestion_runs",
    "raw_tender_records",
    "tenders",
    "tender_summaries",
    "tender_embeddings",
    "business_profile_embeddings",
    "tender_matches",
    "saved_tenders",
    "dismissed_tenders",
    "processing_errors",
}
def main():
    inspector = inspect(engine)
    tables = set(
        inspector.get_table_names()
    )
    missing = EXPECTED_TABLES - tables
    if missing:
        print("Missing tables:")
        print(missing)
        return
    print("All expected tables exist.")
if __name__ == "__main__":
    main()

Run:

python scripts/check_database.py

Expected:

All expected tables exist.

Step 20: Create a Migration Test

Create:

tests/database/test_schema.py

Add:

from sqlalchemy import inspect
from app.database.session import engine
def test_expected_tables_exist():
    inspector = inspect(engine)
    tables = set(
        inspector.get_table_names()
    )
    expected = {
        "users",
        "business_profiles",
        "tender_sources",
        "ingestion_runs",
        "raw_tender_records",
        "tenders",
        "tender_summaries",
        "tender_embeddings",
        "business_profile_embeddings",
        "tender_matches",
        "saved_tenders",
        "dismissed_tenders",
        "processing_errors",
    }
    assert expected.issubset(tables)

Run:

pytest

This validates that migrations created the schema successfully.

Migration Best Practices

One Logical Change Per Migration

Good:

Add tender matches

Bad:

Add matches, alerts, billing, teams, analytics

Small migrations are easier to review and roll back.

Never Edit Applied Migrations

Once committed and applied:

DO NOT MODIFY

Instead:

Create a new migration.

Migration history should remain immutable.

Always Review Autogenerated Code

Alembic helps.

Humans remain responsible.

Review:

• Constraints
• Foreign keys
• Indexes
• Nullability
• Delete behavior

before applying.

Keep Migrations in Git

Migration files are source code.

Commit them with the related model changes.

Current Database Architecture

The database now contains:

Users
 |
Business Profiles
 |
Tender Matches
 |
Tenders
 |
Tender Summaries
 |
Tender Embeddings
Tender Sources
 |
Ingestion Runs
 |
Raw Tender Records
 |
Tenders
Saved Tenders
Dismissed Tenders
Processing Errors

All relationships are enforced by PostgreSQL.

Common Error: Empty Migration

Cause:

Base.metadata

contains no tables.

Usually:

import_models()

was forgotten.

Common Error: Vector Type Missing

Cause:

CREATE EXTENSION vector

was not executed.

Solution:

Add:

op.execute(
    "CREATE EXTENSION IF NOT EXISTS vector"
)

before creating vector columns.

Common Error: Database Not Up To Date

Example:

Target database is not up to date.

Check:

alembic current

Then:

alembic upgrade head

Common Error: Constraint Naming Problems

Usually caused by missing metadata naming conventions.

Verify:

NAMING_CONVENTION

exists in:

Base.metadata

Current Deliverables

At this stage we now have:

✔ PostgreSQL database
✔ Alembic configured
✔ pgvector enabled
✔ Migration workflow
✔ Initial schema
✔ Database version tracking
✔ Upgrade support
✔ Downgrade support
✔ Schema validation
✔ Migration tests

The persistence layer is fully operational.

Recommended Git Commit

Create a clean checkpoint:

git add .
git commit -m "Create initial PostgreSQL schema migration"

This commit represents the first executable version of the complete database architecture.

What We Will Build Next

The database schema now exists physically inside PostgreSQL.

The next logical step is creating the Repository Layer.

Repositories will:

• Encapsulate database queries
• Hide SQLAlchemy details
• Centralize CRUD operations
• Support testing
• Provide reusable persistence logic

We will build repositories for:

• Users
• Business profiles
• Tender sources
• Tenders
• Tender matches
• Saved tenders
• Dismissed tenders

The next article in the series is:

Building the Repository Layer for the AI Tender Opportunity Scanner.

Conclusion

The AI Tender Opportunity Scanner now has a fully versioned PostgreSQL schema managed by Alembic.

The migration system creates all MVP tables, constraints, indexes, and vector storage requirements in a repeatable way. PostgreSQL now enforces the relationships and validation rules defined in the SQLAlchemy models.

The application has crossed an important milestone: the database architecture is no longer conceptual—it is executable, versioned, and testable.

With the persistence foundation complete, we can now begin implementing repositories and application logic on top of the schema.