adelorenzo/konstruct
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
konstruct/.planning/phases/01-foundation/01-01-SUMMARY.md

11 KiB
Raw Permalink Blame History

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

Reference in New Issue View Git Blame Copy Permalink