Databases, Authentication, Authorization, and APIs

A database stores application state. Authentication proves who a user is. Authorization determines what that user may do. An API defines communication boundaries between clients, servers, and other services. Design them together when an application handles user data.

1. Databases, Schemas, ORMs, and Migrations

A schema defines tables, columns, types, constraints, indexes, and relationships. An ORM helps code read and write a database through models or a query API. A migration is an ordered, reviewable schema change. A seed inserts initial or practice data.

Do not change a production database directly through a database UI without a migration and backup plan. A schema file in source code does not mean the runtime database changed. Generate, review, run, and verify migrations in the intended environment.

2. Run PostgreSQL Locally with Docker

An example Compose file for local practice:

services:
  db:
    image: postgres:17
    restart: unless-stopped
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: local-development-only
      POSTGRES_DB: app_local
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

Start and inspect the service:

docker compose up -d
docker compose ps
docker compose logs db

These example credentials are only for a local computer. Never use them in production. Follow the repository's image version and configuration when they already exist.

3. Manage Environment Variables and Secrets

Use .env.local for local values and .env.example for variable names without secrets. Add secret files to .gitignore before the first commit.

DATABASE_URL=postgresql://app:local-development-only@localhost:5432/app_local

Never place a production database URL, token, private key, or password in prompts, screenshots, logs, issues, or a client-side bundle. Treat variables readable by the browser as public.

4. Separate Authentication and Authorization

Authentication answers "who are you?" through passwords, OAuth, passkeys, or another mechanism. Authorization answers "what may you do?" through roles, ownership, permissions, tenants, and resources.

A signed-in user is not automatically allowed to read another user's invoice. Every server operation must check the session and data scope. Hiding a frontend button improves UX but does not create a security boundary.

5. Understand the API Boundary

An API receives a request, validates input, checks authorization, runs business rules, accesses the database, and returns a response. A client must not decide a final price, role, tenant ID, or approval status that belongs to server authority.

For each endpoint, document method, path, input, output, authentication, permission, errors, side effects, and idempotency. Do not expose stack traces, raw queries, credentials, or internal details in public responses.

6. Turn a Blueprint Schema into Migrations

An AI Blueprint Database Schema is a requirement and design artifact, not evidence that the runtime database is ready. Compare it with the repository ORM and migrations. Resolve naming, type mapping, constraints, indexes, foreign keys, cascade behavior, audit fields, and tenant isolation.

For risky changes, use an expand, data migration, code switch, then contract sequence. Important data needs backup and restore rehearsal. Do not remove a column only because the newest UI does not display it.

7. Test Data and Permissions

Minimum tests include:

• valid and invalid input;
• unauthenticated users;
• authenticated users with permission;
• authenticated users without permission;
• rejected cross-user or cross-tenant access;
• missing resources;
• safe retries for idempotent operations;
• transaction rollback when one step fails.

Use synthetic data. Never copy production customer records into a repository or practice environment.

8. Stop, Start, and Reset Local Services

Stop containers without deleting data:

docker compose stop
docker compose start

docker compose down removes containers and networks, while named volumes usually remain. The -v option deletes volumes and their data. Do not use it until you have confirmed that data loss is acceptable.

9. Data and API Quality Gate

• Schema and migrations agree.
• Migrations work on an empty database and relevant upgrade path.
• Secrets are absent from Git and client bundles.
• Every write validates input on the server.
• Authorization applies at the query or service boundary.
• Tenant and ownership boundaries have negative tests.
• Public errors are safe while internal logs remain diagnostic.
• Backup, rollback, or recovery matches the data risk.