This sprint marked a shift in my project. Up to now, I’d been setting up scaffolding: the repo structure, tooling, and CI/CD pipelines. Important work, but invisible to most people. This time, I started building the backbone that the assistant will actually run on: the database and the way my application talks to it.

Getting this right is about more than just making code work. It’s about making thoughtful delivery choices that give the platform credibility. If the database layer isn’t stable, everything else built on top of it (bookings, the FAQ bot, sentiment tracking, forecasting) would eventually collapse. That’s why this sprint focused on decisions, wiring, and resilience before jumping into features.

The work split into four parts:

1. Writing ADR-002 to capture my decision on database choice and migrations.
2. Adding a docker-compose.yml file for Postgres (and optional pgAdmin).
3. Extending .env.example and wiring my settings so the app could build a connection string.
4. Adding src/db/session.py with SQLAlchemy’s async engine and session management.

Along the way, I ran into a few bumps which I’ll share, as those are often where the real learning happens.

ADR-002: Database Choice and Migrations

The first job was to make a decision: which database to use and how to manage schema changes. I chose Postgres, with Alembic handling migrations.

Why Postgres? It’s widely trusted, open-source, and built for enterprise workloads. You could think of Postgres as the equivalent of a commercial airliner: robust, dependable, and capable of carrying heavy loads. SQLite, by contrast, is more like a scooter. It’s light, quick to get started, and perfect for a single person zipping around town, but it’s not what you’d use to move a hundred passengers across the Atlantic.

Why Alembic? Databases evolve over time. You might add a table for bookings one week, then change a column type the next. Alembic keeps a versioned history of those changes, a bit like having a recipe book that records not only the finished dish but also every substitution and tweak you made along the way.

Capturing this choice in ADR-002 means I’ve documented not only the outcome but the alternatives considered and the rationale. In a large enterprise project, that record would be vital for governance and future audits.

Docker and Postgres

Next came getting Postgres up and running. I could have installed it directly on my laptop, but that introduces all sorts of problems: differences between machines, version mismatches, and configuration headaches. Instead, I used Docker.

Docker is like a shipping container for software. Once your database is packaged up inside it, you can run it anywhere without worrying about what’s installed underneath. Whether it’s my MacBook, a Linux server, or a cloud platform, the container behaves the same way.

Here’s a simplified snippet of my docker-compose.yml:

services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: ${APP_DB_USER:-appuser}
      POSTGRES_PASSWORD: ${APP_DB_PASSWORD:-apppass}
      POSTGRES_DB: ${APP_DB_NAME:-appdb}
    ports:
      - "${APP_DB_PORT:-5432}:5432"
    volumes:
      - db_data:/var/lib/postgresql/data

This tells Docker to pull a lightweight version of Postgres (postgres:16-alpine), set it up with a user, password, and database name, expose the default port, and persist the data to a local volume so it survives restarts.

I also added pgAdmin as an optional service. pgAdmin is essentially a control tower for Postgres: it provides a visual dashboard to see what’s happening inside the database without needing to type SQL commands manually.

Issues we hit:

• At first, my Makefile failed to run docker compose up -d db. The culprit was that Makefiles require tabs, not spaces. Fixing the indentation sorted it out.
• Then I ran into “docker: command not found”. That turned out to be because Docker Desktop wasn’t installed or running on my Mac. Installing Docker Desktop resolved the issue.

Lesson learned: small setup details matter. Automation scripts can fail for the tiniest reasons, and containers only work if you’ve got the base tooling installed.

Environment Variables and Connection Strings

Once the database was running, the app needed to know how to connect to it. Hardcoding usernames and passwords into code is a bad idea, both from a security and flexibility standpoint. The better practice is to keep them in environment variables.

In .env.example, I defined the key variables:

APP_DB_USER=appuser
APP_DB_PASSWORD=apppass
APP_DB_NAME=appdb
APP_DB_HOST=localhost
APP_DB_PORT=5432

APP_DB_URL=postgresql+asyncpg://${APP_DB_USER}:${APP_DB_PASSWORD}@${APP_DB_HOST}:${APP_DB_PORT}/${APP_DB_NAME}

This file is a template: it shows what values are needed without including any real secrets. The actual .env file, which is ignored by Git, contains the real credentials.

The important bit here is the connection string. A connection string is like the address and key for a building. It tells your app where the database lives (the street address), what door to knock on (the port), and how to get in (the username and password). Without it, your app wouldn’t know how to reach the database.

I wired this into settings.py using Pydantic’s BaseSettings, so the app can either use the full APP_DB_URL if it’s provided, or build one from the pieces. That makes it flexible: I can switch to a cloud-hosted Postgres later by just changing the .env file, no code edits required.

SQLAlchemy and Async Sessions

Finally, I needed to set up the application’s side of the database connection. This is where SQLAlchemy comes in. SQLAlchemy is a Python library that lets you work with databases using Python code instead of raw SQL.

Here’s the core of src/db/session.py:

engine = create_async_engine(
    settings.app_db_url,
    echo=False,
    pool_pre_ping=True,
)

AsyncSessionLocal = async_sessionmaker(
    bind=engine,
    expire_on_commit=False,
    autoflush=False,
)

async def get_db() -> AsyncIterator[AsyncSession]:
    async with AsyncSessionLocal() as session:
        yield session

Breaking this down:

• create_async_engine sets up the database engine. The async version means it can handle multiple requests without blocking. It’s like a kitchen that can keep boiling pasta while also chopping vegetables.
• async_sessionmaker creates sessions, which are temporary workspaces for running queries. Each request gets its own session.
• get_db() is a FastAPI dependency that hands out a session to each API request and ensures it’s closed afterwards. This prevents resource leaks, the software equivalent of leaving the lights on in an empty room.

Issues we hit:

• At first, I ran into errors importing BaseSettings. Pydantic had moved it into a separate package (pydantic-settings) in version 2. Installing that package and updating the imports fixed it.
• Then Pydantic complained about PGADMIN_EMAIL and PGADMIN_PASSWORD being “extra fields”. The fix was to add them to the settings class with extra="ignore" so the validator didn’t choke.

Lesson learned: libraries evolve fast. Even well-trodden packages like Pydantic make breaking changes. Reading migration guides and pinning versions can save a lot of debugging pain.

What I’d Do Differently in an Enterprise Context

In an enterprise delivery, the principles would be the same but the execution would be different.

• I’d use a managed Postgres service in the cloud (such as AWS RDS or Google Cloud SQL) rather than Dockerised Postgres on a laptop. The goal in enterprise is resilience and scale, not just local reproducibility.
• Secrets would be handled by a secure secrets manager instead of .env files. This provides centralised access controls, auditing, and compliance.
• Migrations would go through formal governance. In a solo project, I can apply Alembic migrations directly. In enterprise, every schema change would be peer-reviewed, tested in lower environments, and approved before reaching production.

The trade-off is speed. As a solo builder, I can make changes quickly and learn fast. In a large organisation, the same tasks might take longer but provide safety, accountability, and compliance. Both approaches are valid – the difference lies in context and objectives.

Wrapping Up

This sprint didn’t deliver a shiny feature you can click on, but it did deliver something more important: trust in the foundation. Postgres is running in Docker, environment variables keep configuration safe and flexible, SQLAlchemy manages clean sessions, and Alembic is ready to track migrations as the schema evolves.

The problems I hit – from Makefile tabs to Pydantic package changes – were a reminder that details matter. Each fix was small, but the lessons are big: automation is brittle without precision, and libraries move quickly enough that you need to stay on top of changes.

The details may seem small, but getting them right is what turns a scrappy demo into a system you can trust. That’s the difference between hacking something together and deliberately building a platform. The backbone is now in place, and the next step is to use it for the first visible feature: booking CRUD.