Перейти к содержимому
Размах

F-04 SQLAlchemy 2 async + Alembic + RLS (8 traps captured)

Outcome (~2 часа vs 1 день plan)

  • ✓ SQLAlchemy 2.0.49 async + asyncpg 0.31.0 + Alembic 1.18.4 + pgvector 0.4.2
  • ✓ 3 baseline tables (organization, user, marketplace_account) + raw.payload monthly-partitioned
  • ✓ 3 RLS policies + ON UPDATE trigger
  • ✓ UUID v7 PG18 native через uuidv7()
  • ✓ ICU ru_RU collations created
  • ✓ 7/7 integration tests pass на real PG18 (включая cross-tenant probe — P15 critical)

8 traps encountered + fixed

1. PG bootstrap schema chicken-and-egg

Alembic нужна core.alembic_version table для tracking, но schema core создаётся в первой migration. Решение: bootstrap CREATE SCHEMA core AUTHORIZATION razmakh_admin вручную перед первым alembic upgrade head. После этого все migrations работают autonomously.

2. PG SET LOCAL не поддерживает bind parameters

# FAILS:
await session.execute(text("SET LOCAL app.organization_id = :val"), {"val": str(org_id)})
# → syntax error at or near "$1"


# WORKS:
await session.execute(
    text("SELECT set_config(:name, :val, true)"),
    {"name": "app.organization_id", "val": str(org_id)},
)

set_config(name, val, is_local) — PG built-in function с parametrization. is_local=true ≡ SET LOCAL (transaction scope).

3. postgres user — BYPASSRLS attribute

FORCE ROW LEVEL SECURITY enforces RLS для table owner, но NOT для BYPASSRLS roles (включая superusers). Integration tests connecting as postgres тратили часы возвращая 1+ rows когда expected 0.

Fix: integration tests должны connect от razmakh_app (NO BYPASSRLS). Admin session для setup separate.

@asynccontextmanager
async def admin_session():
    """postgres user — bypass RLS для setup."""


# Tests use razmakh_app session через get_session()

Это правильный pattern для production — application connects as razmakh_app, admin operations отдельно.

4. mypy ClassVar incompatible с SQLAlchemy declarative

class Organization(Base):
    __table_args__: ClassVar[dict[str, str]] = {"schema": "core"}
    # → error: Cannot override instance variable on base class

DeclarativeBase declares __table_args__ as instance var. ClassVar override breaks.

Fix: ignore RUF012 для models directory + use plain dict (SQLAlchemy convention).

[tool.ruff.lint.per-file-ignores]
"**/models/**/*.py" = ["RUF012"]

5. Pytest namespace conflict (apps/api/tests vs packages/core/tests)

Оба тестовых dirs имели __init__.py → pytest collection failed “ModuleNotFoundError: tests.test_secret_ref” because two tests packages в namespace.

Fix:

  • Delete __init__.py в tests/ dirs
  • pytest: --import-mode=importlib для proper module resolution без packages

6. mypy py.typed marker required

error: Package 'razmakh_api' cannot be type checked due to missing py.typed marker.

Fix: empty py.typed file в каждом package root. PEP 561 convention.

7. SQLAlchemy DeclarativeBase repr on uninitialized objects

getattr(self, c.name) на Base.__init__ may fail если id ещё не populated (perceived AttributeError для unset attributes).

Fix: getattr(self, c.name, None) default. Plus iterate __table__.primary_key directly не .columns (SQLAlchemy 2.x API).

8. Pytest-asyncio event loop closed (module-scope engine)

@pytest.fixture(scope="module") для engine + await dispose_engine() в teardown → next test’s loop closed.

Fix: function-scoped fixture (init + dispose per test). Slower но reliable. Alternative: configure loop_scope="module" for newer pytest-asyncio.

Architecture decisions

RLS enforcement model

  • core.organization — RLS USING id = org_id (self-only)
  • core.marketplace_account — RLS USING + WITH CHECK organization_id = org_id
  • core.user — NO RLS (per decision, через SECURITY DEFINER function в Phase 1.5)
  • raw.payload — RLS same scope (ETL bronze isolated per-org)

UUID v7 default

PG18 ships uuidv7() native — нет need в pg_uuidv7 extension. Все PKs use server_default = uuidv7(). Sortable, unguessable, distributed-safe.

ICU collations

ru_RU_icu (CI) + ru_RU_icu_cs (CS) created в migration. Per-column usage в Phase 1+ для name/title fields. PG18 native ICU provider.

Pool config

pool_size=20 max_overflow=10 pool_pre_ping=true pool_recycle=3600. Baseline для Phase 1 — Николай soло dev + few backend processes. Phase 1.5 (10 партнёров) — possibly увеличить.

What this enables

  • F-05 Manifest validator может использовать Base.metadata для проверки schema invariants
  • F-06 ETL Collector class пишет в raw.payload через session + RLS
  • F-07 Proxy pool sessions для per-account requests
  • F-08 FastAPI startup: init_engine() → Infisical.resolve_secret → engine ready
  • F-09 CI: docker compose up postgres + alembic upgrade + run tests
  • Cross-tenant probe (Block 1.5.5) baseline established — automate в CI

Deferred

  • core.user SECURITY DEFINER app.visible_users(org_id) — F-05 или Block 1.5.1
  • Separate admin/app passwords через Infisical — F-05
  • semgrep rule запрет raw Session() — TBD (currently CI ruff)
  • pg_exporter dedicated read-only metrics role — F-10
  • Auto-partition raw.payload cron — F-06
  • Async test fixtures session-scoped (когда pytest-asyncio supports cleanly)

Numbers

  • Lines of code added: ~900 (models + db layer + alembic + tests + docs)
  • Lines per test: ~25 average
  • Migration size: 270 lines (3 tables + 3 RLS + raw partition + triggers)
  • Test time: 0.76 sec для 7 integration tests против real PG
  • Real PG schema: 7 tables (4 system, 3 core + 1 raw partitioned)