# viper-db — Shared Database Layer

Shared Python package (`lifestyle-labs-viper-db`) consumed by viper-api, viper-agents, and viper-data. Python >=3.12, built with Hatchling.

## Tech Stack

- **ORM**: SQLModel (SQLAlchemy + Pydantic hybrid)
- **Async Driver**: asyncpg (PostgreSQL)
- **Migrations**: Alembic
- **Config**: Dynaconf (`config/settings.json` + `config/settings.local.json`)
- **Database**: Supabase-hosted PostgreSQL
- **Linting**: Ruff (line-length 90, rules E/F/I)
- **Package Manager**: uv

## Directory Structure

```
src/viper_db/
├── client.py          # DbClient: async engine + ContextVar-based session management
├── config.py          # Dynaconf settings loader
├── models/            # SQLModel table definitions (single source of truth for schema)
│   ├── __init__.py    # Re-exports all models — new models MUST be registered here
│   ├── _base.py       # Shared column helpers: uuid_pk_field(), utcnow_field()
│   └── *.py           # One file per table (user.py, restaurant.py, feedback.py, etc.)
├── dao/               # Data Access Objects — async CRUD operations
│   ├── __init__.py    # Re-exports all DAOs + init_dao() — new DAOs MUST be registered here
│   └── *.py           # One file per table, each exports a DAO class + singleton instance
└── utils/
    └── db.py          # get_session() helper that delegates to the default DbClient
```

## Architecture Patterns

- **DbClient** (`client.py`): Holds an `AsyncEngine` and `async_sessionmaker`. Session access uses a `ContextVar` — the consuming service (viper-api) sets the session per-request via middleware.
- **DAO singletons**: Each DAO file exports a class and a module-level singleton (e.g., `feedback_dao = FeedbackDAO()`). DAOs access the session via `get_session()` from `utils/db.py`.
- **Models are the schema source of truth**: Alembic's `env.py` imports `viper_db.models` to populate `target_metadata` for autogenerate.

## Adding a New Table

1. Create `src/viper_db/models/<table>.py` with a `SQLModel` class (`table=True`).
2. Register it in `src/viper_db/models/__init__.py` (import + `__all__`).
3. Create `src/viper_db/dao/<table>.py` with a DAO class + singleton.
4. Register it in `src/viper_db/dao/__init__.py` (import + `__all__`).
5. Create an Alembic migration (see below).

## Alembic Migrations

Migrations live in `alembic/versions/`. The chain uses sequential four-digit IDs:

```
0001 → 0002 → 0003 → 0004 → 0005 → 0006 → 0007 → 0008 → 0009 → 0010
```

When adding a new migration, use the next sequential number (e.g., `0011`).

### Running Migrations

Alembic reads the DB URL from Dynaconf (`SUPABASE.db_url`). You need a `config/settings.local.json` with the connection string:

```json
{
  "dynaconf_merge": true,
  "default": {
    "SUPABASE": {
      "db_url": "postgresql://postgres:<password>@db.<project>.supabase.co:5432/postgres"
    }
  }
}
```

This file is gitignored. Once it exists, run:

```bash
.venv/bin/alembic upgrade head     # Apply all pending migrations
.venv/bin/alembic downgrade -1     # Roll back one migration
.venv/bin/alembic history          # Show migration chain
.venv/bin/alembic current          # Show current revision in the database
```

### Creating a New Migration

- **By hand** (preferred for simple DDL): Create a file in `alembic/versions/` following the naming convention `NNNN_description.py`. Set `revision` to the next sequential number and `down_revision` to the current head.
- **Autogenerate**: `.venv/bin/alembic revision --autogenerate -m "description"` — compares models against the live DB and generates upgrade/downgrade ops.

### Important: `env.py` Filters

`alembic/env.py` has an `_include_object` filter that excludes:
- The `users_id_fkey` constraint (FK to `auth.users`, managed by Supabase)
- Any FK referencing the `auth` schema

This means the `users.id → auth.users(id)` relationship is NOT managed by Alembic. It's maintained via a Supabase-side trigger/migration.

## Supabase-Specific Notes

- The `users` table PK is a UUID that mirrors `auth.users(id)` in Supabase. A `handle_new_user()` trigger in Supabase auto-creates rows in `public.users` when users sign up.
- RLS policies are not managed by this package. Since all DB access goes through the FastAPI server (viper-api), RLS is not relied upon.
- Supabase also has its own migration system (visible via the dashboard). Alembic migrations are the authoritative source for the `public` schema; avoid creating conflicting Supabase dashboard migrations for tables Alembic manages.

## Build & Release

```bash
make build        # python3 -m build
make bump-patch   # Bump patch version, commit, tag, push (triggers PyPI publish)
make bump-minor   # Bump minor version, commit, tag, push
```

## Changelog

Previous coding agents have documented changes in `docs/changelog/`. If you make meaningful schema or DAO changes, add a new changelog entry there.
