206 lines
11 KiB
Markdown
206 lines
11 KiB
Markdown
---
|
||
phase: 01-foundation
|
||
plan: 01
|
||
subsystem: database
|
||
tags: [uv, fastapi, pydantic, sqlalchemy, asyncpg, alembic, postgresql, redis, rls, docker-compose, multi-tenancy]
|
||
|
||
# Dependency graph
|
||
requires: []
|
||
provides:
|
||
- uv workspace monorepo with 5 member packages (shared, gateway, router, orchestrator, llm-pool)
|
||
- KonstructMessage Pydantic model (unified internal message format)
|
||
- Tenant, Agent, ChannelConnection, PortalUser SQLAlchemy 2.0 ORM models
|
||
- PostgreSQL RLS with FORCE ROW LEVEL SECURITY on tenant-scoped tables
|
||
- async SQLAlchemy engine + get_session FastAPI dependency
|
||
- current_tenant_id ContextVar + configure_rls_hook for RLS injection
|
||
- Redis key namespace constructors (rate_limit, idempotency, session, engaged_thread)
|
||
- Alembic async migration env with 001_initial_schema migration
|
||
- Docker Compose dev environment (PostgreSQL 16, Redis 7, Ollama)
|
||
- Green test suite: 33 unit tests + 7 integration tests proving RLS isolation
|
||
affects:
|
||
- 01-02 (LiteLLM pool imports shared models and db)
|
||
- 01-03 (Channel Gateway + Message Router import KonstructMessage, tenant resolution)
|
||
- 01-04 (Portal imports PortalUser model, uses get_session)
|
||
- All subsequent plans depend on shared data layer
|
||
|
||
# Tech tracking
|
||
tech-stack:
|
||
added:
|
||
- uv 0.9.10 (workspace package manager)
|
||
- Python 3.13 (runtime — CLAUDE.md specifies 3.12+, 3.13 works in practice)
|
||
- FastAPI 0.115+ (API framework)
|
||
- Pydantic v2 2.12+ (data models + settings)
|
||
- pydantic-settings 2.8+ (config loading)
|
||
- SQLAlchemy 2.0.48 (async ORM)
|
||
- asyncpg 0.31 (PostgreSQL async driver)
|
||
- Alembic 1.14+ (DB migrations)
|
||
- Redis 6.4 (Python client)
|
||
- Celery 5.4 (background tasks)
|
||
- PostgreSQL 16 (Docker)
|
||
- Redis 7 (Docker)
|
||
- Ollama (Docker, GPU-optional)
|
||
- pytest-asyncio 1.3 (async test support)
|
||
- fakeredis 2.34 (Redis mock for unit tests)
|
||
patterns:
|
||
- SQLAlchemy 2.0 Mapped[]/mapped_column() style (never 1.x Column())
|
||
- ContextVar-based RLS injection via before_cursor_execute event hook
|
||
- UUID-sanitized SET LOCAL to prevent SQL injection in RLS path
|
||
- FORCE ROW LEVEL SECURITY on all tenant-scoped tables
|
||
- All app code connects as konstruct_app role (not postgres superuser)
|
||
- Redis key format: {tenant_id}:{type}:{discriminator} — no bare keys possible
|
||
- Alembic migrations run as admin user; app runs as limited konstruct_app role
|
||
- pytest-asyncio auto mode; session-scoped DB created via subprocess Alembic run
|
||
|
||
key-files:
|
||
created:
|
||
- pyproject.toml
|
||
- docker-compose.yml
|
||
- .env.example
|
||
- alembic.ini
|
||
- scripts/init-db.sh
|
||
- packages/shared/pyproject.toml
|
||
- packages/shared/shared/config.py
|
||
- packages/shared/shared/db.py
|
||
- packages/shared/shared/rls.py
|
||
- packages/shared/shared/redis_keys.py
|
||
- packages/shared/shared/models/message.py
|
||
- packages/shared/shared/models/tenant.py
|
||
- packages/shared/shared/models/auth.py
|
||
- migrations/env.py
|
||
- migrations/script.py.mako
|
||
- migrations/versions/001_initial_schema.py
|
||
- tests/conftest.py
|
||
- tests/unit/test_normalize.py
|
||
- tests/unit/test_tenant_resolution.py
|
||
- tests/unit/test_redis_namespacing.py
|
||
- tests/integration/test_tenant_isolation.py
|
||
modified: []
|
||
|
||
key-decisions:
|
||
- "PostgreSQL RLS with FORCE ROW LEVEL SECURITY chosen for Starter-tier tenant isolation — prevents accidental superuser bypass"
|
||
- "SET LOCAL app.current_tenant sanitized via UUID round-trip (not parameterized) — asyncpg does not support prepared statement placeholders for SET LOCAL"
|
||
- "channel_type stored as TEXT with CHECK constraint in migration rather than native ENUM — avoids SQLAlchemy auto-emitting duplicate CREATE TYPE DDL"
|
||
- "Alembic migrations run as postgres admin; application connects as konstruct_app — role separation is what makes RLS real"
|
||
- "Session-scoped test DB with subprocess Alembic migration + function-scoped AsyncEngine — avoids pytest-asyncio cross-loop-scope issues with SQLAlchemy"
|
||
- "uv workspace with --all-packages sync required for workspace member packages to be installed in editable mode"
|
||
|
||
patterns-established:
|
||
- "RLS pattern: ContextVar.set() → execute SQL → ContextVar.reset() using token"
|
||
- "Test isolation pattern: fresh test DB per session, function-scoped engine connecting as app role"
|
||
- "Key namespacing pattern: all Redis keys MUST start with {tenant_id}:"
|
||
|
||
requirements-completed: [CHAN-01, TNNT-01, TNNT-02, TNNT-03, TNNT-04]
|
||
|
||
# Metrics
|
||
duration: 12min
|
||
completed: 2026-03-23
|
||
---
|
||
|
||
# Phase 01 Plan 01: Monorepo Scaffolding, Data Models, and RLS Tenant Isolation Summary
|
||
|
||
**uv workspace monorepo with PostgreSQL RLS proving Tenant A cannot access Tenant B's data through SQLAlchemy + asyncpg with FORCE ROW LEVEL SECURITY enforced at schema level**
|
||
|
||
## Performance
|
||
|
||
- **Duration:** 12 min
|
||
- **Started:** 2026-03-23T15:45:02Z
|
||
- **Completed:** 2026-03-23T15:57:51Z
|
||
- **Tasks:** 2
|
||
- **Files modified:** 32
|
||
|
||
## Accomplishments
|
||
|
||
- uv workspace monorepo with 5 member packages — all importable with `uv run python -c "from shared.models.message import KonstructMessage"`
|
||
- PostgreSQL RLS tenant isolation proven green: 7 integration tests confirm cross-tenant data leakage is impossible via `FORCE ROW LEVEL SECURITY` on agents and channel_connections tables
|
||
- Alembic async migration creates all tables, grants to konstruct_app role, and configures RLS policies via `current_setting('app.current_tenant', TRUE)::uuid`
|
||
- Docker Compose dev environment boots cleanly: PostgreSQL 16 + Redis 7 + Ollama (GPU-optional)
|
||
- 33 unit tests for KonstructMessage normalization, tenant resolution logic, and Redis key namespacing
|
||
|
||
## Task Commits
|
||
|
||
Each task was committed atomically:
|
||
|
||
1. **Task 1: Monorepo scaffolding, Docker Compose, and shared data models** - `5714acf` (feat)
|
||
2. **Task 2: Alembic migrations with RLS and tenant isolation tests** - `47e7862` (feat)
|
||
|
||
**Plan metadata:** (docs commit — forthcoming)
|
||
|
||
## Files Created/Modified
|
||
|
||
- `pyproject.toml` — uv workspace root with 5 members, pytest config, ruff/mypy settings
|
||
- `docker-compose.yml` — PostgreSQL 16, Redis 7, Ollama with shared konstruct-net
|
||
- `.env.example` — all env vars documented with konstruct_app role (not superuser)
|
||
- `packages/shared/shared/models/message.py` — KonstructMessage, ChannelType, SenderInfo, MessageContent
|
||
- `packages/shared/shared/models/tenant.py` — Tenant, Agent, ChannelConnection (SQLAlchemy 2.0 Mapped[] style)
|
||
- `packages/shared/shared/models/auth.py` — PortalUser for portal authentication
|
||
- `packages/shared/shared/db.py` — async engine, session factory, get_session FastAPI dependency
|
||
- `packages/shared/shared/rls.py` — current_tenant_id ContextVar, configure_rls_hook (idempotent, UUID-sanitized)
|
||
- `packages/shared/shared/redis_keys.py` — rate_limit_key, idempotency_key, session_key, engaged_thread_key
|
||
- `packages/shared/shared/config.py` — Pydantic Settings loading all env vars
|
||
- `migrations/versions/001_initial_schema.py` — full schema with RLS policies and FORCE ROW LEVEL SECURITY
|
||
- `tests/integration/test_tenant_isolation.py` — 7 tests proving RLS isolation across both tenant-scoped tables
|
||
|
||
## Decisions Made
|
||
|
||
- **SET LOCAL parameterization:** asyncpg does not support `%s` placeholders for `SET LOCAL` statements. Switched to f-string with UUID round-trip sanitization — `str(UUID(str(tenant_id)))` ensures only valid hex+hyphen characters reach the DB.
|
||
- **channel_type as TEXT:** Native `sa.Enum` type caused SQLAlchemy to auto-emit a second `CREATE TYPE channel_type_enum` statement in `create_table`, conflicting with our explicit `CREATE TYPE`. Stored as `TEXT` with a `CHECK (channel_type IN (...))` constraint instead.
|
||
- **Function-scoped AsyncEngine in tests:** pytest-asyncio 1.3.0 raises cross-loop-scope errors with session-scoped async fixtures. Moved engine creation to function scope; used a session-scoped synchronous fixture to create the test DB and run migrations once via subprocess.
|
||
|
||
## Deviations from Plan
|
||
|
||
### Auto-fixed Issues
|
||
|
||
**1. [Rule 1 - Bug] asyncpg does not support %s parameterized SET LOCAL**
|
||
- **Found during:** Task 2 (integration test execution)
|
||
- **Issue:** `rls.py` used `cursor.execute("SET LOCAL app.current_tenant = %s", (tenant_id,))` — asyncpg raises `PostgresSyntaxError: syntax error at or near "%"` because asyncpg uses `$1` style and SET LOCAL doesn't accept prepared statement parameters at all
|
||
- **Fix:** Changed to `cursor.execute(f"SET LOCAL app.current_tenant = '{safe_id}'"`) where `safe_id = str(UUID(str(tenant_id)))` sanitizes the value
|
||
- **Files modified:** `packages/shared/shared/rls.py`
|
||
- **Verification:** All 7 integration tests pass including cross-tenant isolation test
|
||
- **Committed in:** `47e7862` (Task 2 commit)
|
||
|
||
**2. [Rule 1 - Bug] SQLAlchemy double-emits CREATE TYPE for sa.Enum in create_table**
|
||
- **Found during:** Task 2 (alembic upgrade head execution)
|
||
- **Issue:** `sa.Enum(..., create_type=False)` in `op.create_table` still triggered a second `CREATE TYPE channel_type_enum` DDL, causing `DuplicateObjectError` even when our explicit `CREATE TYPE` ran first
|
||
- **Fix:** Changed `channel_type` column to `sa.Text` with a `CHECK` constraint enforcing valid values
|
||
- **Files modified:** `migrations/versions/001_initial_schema.py`
|
||
- **Verification:** `alembic upgrade head` completes cleanly; constraint visible in `pg_constraint`
|
||
- **Committed in:** `47e7862` (Task 2 commit)
|
||
|
||
**3. [Rule 3 - Blocking] Root pyproject.toml lacked build-system for uv workspace root**
|
||
- **Found during:** Task 1 (uv sync execution)
|
||
- **Issue:** Including `[build-system]` in root `pyproject.toml` caused hatchling to fail looking for a `konstruct` package directory; workspace roots should not be buildable packages
|
||
- **Fix:** Removed `[build-system]` from root `pyproject.toml`; used `uv sync --all-packages` to install workspace members
|
||
- **Files modified:** `pyproject.toml`
|
||
- **Verification:** `uv sync --all-packages` succeeds; all workspace packages listed in `uv pip list`
|
||
- **Committed in:** `5714acf` (Task 1 commit)
|
||
|
||
---
|
||
|
||
**Total deviations:** 3 auto-fixed (2 bugs, 1 blocking)
|
||
**Impact on plan:** All fixes necessary for correctness. No scope creep.
|
||
|
||
## Issues Encountered
|
||
|
||
- pytest-asyncio 1.3.0 cross-loop-scope issue with session-scoped async `db_engine` fixture — resolved by making engine function-scoped and using a synchronous session-scoped fixture for one-time DB creation
|
||
- `::jsonb` cast syntax in SQLAlchemy `text()` query rejected by asyncpg parameter parser — removed cast, PostgreSQL coerces JSON text implicitly
|
||
|
||
## User Setup Required
|
||
|
||
None — the Docker Compose environment provides all required services. Copy `.env.example` to `.env` to start.
|
||
|
||
## Next Phase Readiness
|
||
|
||
- All packages/shared imports available for Plans 02–04
|
||
- `DATABASE_URL` must use `konstruct_app` role (not superuser) — documented in `.env.example`
|
||
- `DATABASE_ADMIN_URL` must be set for `alembic upgrade head` in CI/CD
|
||
- Docker Compose boots cleanly with `docker compose up -d postgres redis`
|
||
- Ollama GPU reservation is optional — service starts without GPU
|
||
|
||
## Self-Check: PASSED
|
||
|
||
All created files verified present. All task commits verified in git history.
|
||
|
||
---
|
||
*Phase: 01-foundation*
|
||
*Completed: 2026-03-23*
|