Database & Migrations

Jinbocho uses PostgreSQL 16 with one database per service. Migrations are managed by Alembic and run automatically on service startup.

Databases

Database	Service	Local Port
auth_db	auth-service	5432
catalog_db	catalog-service	5433
ai_db	ai-service (optional)	5434

In Docker Compose these ports are bound to 127.0.0.1 only — they are not accessible from outside the machine.

Automatic Migrations on Startup

Both auth-service and catalog-service run alembic upgrade head automatically before starting uvicorn (configured in their Dockerfile CMD). You do not need to run migrations manually in local development or production.

This means: when you run docker compose up, the databases are always up to date with the latest schema.

Local Database Access

Connect with psql

# auth_db
psql -U postgres -h 127.0.0.1 -p 5432 -d auth_db

# catalog_db
psql -U postgres -h 127.0.0.1 -p 5433 -d catalog_db

# ai_db (if running)
psql -U postgres -h 127.0.0.1 -p 5434 -d ai_db

Password: postgres (local dev only).

Useful psql Commands

-- List all tables
\dt

-- Show table schema
\d users
\d owned_books

-- Quick query
SELECT id, email, role FROM users LIMIT 10;

-- Exit
\q

Alembic Workflow

Check Current Migration State

cd jinbocho-auth-v1   # or jinbocho-catalog-v1
source .venv/bin/activate
alembic current       # shows current revision
alembic history       # shows full migration history

Create a New Migration

After changing a SQLAlchemy model:

alembic revision --autogenerate -m "add language column to users"
# Review the generated file in migrations/versions/
# Edit if autogenerate missed something
alembic upgrade head   # test it locally before committing

Always review autogenerated migrations before committing. Alembic can miss: - Custom index types - Partial indexes - Default values for existing rows

Apply Migrations Manually

alembic upgrade head        # apply all pending migrations
alembic upgrade +1          # apply one migration forward
alembic downgrade -1        # revert one migration
alembic downgrade <revision> # revert to a specific revision

Migration on Production (Neon)

Migrations run automatically at service startup. If you need to run them manually against the Neon database:

# Set DATABASE_URL to the Neon connection string
export DATABASE_URL="postgresql+asyncpg://user:pass@ep-xxx.neon.tech/auth_db?ssl=require"

cd jinbocho-auth-v1
source .venv/bin/activate
alembic upgrade head

Test migrations locally first

Always test migrations on a local database before applying to production. A failed migration on Neon can leave the schema in a partial state.

Auth Service Schema

-- families
CREATE TABLE families (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

-- users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    family_id UUID NOT NULL REFERENCES families(id) ON DELETE CASCADE,
    email VARCHAR UNIQUE NOT NULL,
    hashed_password VARCHAR NOT NULL,
    full_name VARCHAR NOT NULL,
    role VARCHAR NOT NULL DEFAULT 'viewer',   -- admin | editor | viewer
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

-- refresh_tokens (server-side revocation)
CREATE TABLE refresh_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash VARCHAR UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    revoked BOOLEAN NOT NULL DEFAULT false,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

Catalog Service Schema

-- rooms
CREATE TABLE rooms (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    family_id UUID NOT NULL,
    name VARCHAR NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

-- bookcases
CREATE TABLE bookcases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL
);

-- sections (vertical columns)
CREATE TABLE sections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bookcase_id UUID NOT NULL REFERENCES bookcases(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    position INTEGER NOT NULL
);

-- shelves (horizontal rows)
CREATE TABLE shelves (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    section_id UUID NOT NULL REFERENCES sections(id) ON DELETE CASCADE,
    name VARCHAR NOT NULL,
    position INTEGER NOT NULL
);

-- bibliographic_records (one per ISBN / edition)
CREATE TABLE bibliographic_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    family_id UUID NOT NULL,
    title VARCHAR NOT NULL,
    author VARCHAR,
    isbn VARCHAR,
    publisher VARCHAR,
    published_year INTEGER,
    cover_url VARCHAR,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

-- owned_books (physical copies)
CREATE TABLE owned_books (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bibliographic_record_id UUID NOT NULL REFERENCES bibliographic_records(id),
    shelf_id UUID REFERENCES shelves(id) ON DELETE SET NULL,
    position INTEGER,
    reading_status VARCHAR NOT NULL DEFAULT 'to_read',  -- to_read | reading | read
    added_by UUID,     -- user_id from auth-service (not FK, cross-service)
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    updated_at TIMESTAMP NOT NULL DEFAULT now()
);

-- isbn_cache (TTL-based metadata cache)
CREATE TABLE isbn_cache (
    isbn VARCHAR PRIMARY KEY,
    data JSONB NOT NULL,
    cached_at TIMESTAMP NOT NULL DEFAULT now()
);

-- audit_log (movement + status history)
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    owned_book_id UUID NOT NULL REFERENCES owned_books(id) ON DELETE CASCADE,
    event_type VARCHAR NOT NULL,   -- moved | status_changed | created
    payload JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    user_id UUID
);

Reset Local Databases

To wipe all local data and start fresh:

cd jinbocho-infrastructure-v1
docker compose down -v   # removes containers AND volumes
docker compose up --build -d   # recreates everything from scratch
# Migrations run automatically on startup

Data loss

docker compose down -v deletes all local data permanently. Only use this in development, never against production.