Database architecture

P11: production-grade с Day 1. P15: organization_id RLS scope обязателен. P17: secrets через Infisical.

Stack

PostgreSQL 18.4 (razmakh/postgres:pg18-pgvector-pgbackrest custom image)
SQLAlchemy 2.0.49 async + asyncpg 0.31.0 driver
Alembic 1.18.4 для migrations (async env.py)
pgvector 0.8.2 для embeddings (RAG / similarity)

Connection (async)

from razmakh_api.db import init_engine, get_session

# Startup
init_engine(database_url="postgresql+asyncpg://razmakh_app:PASS@postgres:5432/razmakh")

# Usage
async with get_session() as session:
    result = await session.execute(select(Organization))

Pool config (production defaults — P11)

Parameter	Value	Rationale
`pool_size`	20	Baseline concurrency Phase 1
`max_overflow`	10	Burst capacity до 30 connections
`pool_pre_ping`	True	Detect stale connections перед use
`pool_recycle`	3600s	Recycle every 1h (PG idle disconnect)
`expire_on_commit`	False	Объекты доступны после commit без re-fetch
`autoflush`	False	Manual flush — производительность

P22 runtime tunable через admin panel (Phase 1 F-12).

Roles (3 levels)

Role	Auth	Privileges	Usage
`postgres`	bootstrap	SUPERUSER, BYPASSRLS	only init / admin scripts
`razmakh_admin`	password	CREATEDB, owner core.*	Alembic migrations
`razmakh_app`	password	NO SUPERUSER, NO BYPASSRLS	application runtime — RLS enforced

Phase 0: один пароль (POSTGRES_PASSWORD) для всех. Phase 1.5 (F-05+) — разделим admin/app в Infisical.

Schemas

Schema	Purpose
`raw`	ETL bronze layer — JSONB monthly-partitioned (WB API responses)
`core`	Normalized entities (silver) — organization, user, marketplace_account, products etc.
`mart`	Materialized views (gold) — dashboard aggregates
`ops`	Operational state — action_queue, job_run, runtime_config

UUID v7 PK (PG18 native)

SELECT uuidv7();  -- → 019e3646-6897-7fa6-8e50-1d5bb28e87b9

UUID v7 = 48-bit ms timestamp + random tail. Sortable (insertion order = ID order) + unguessable (P15) + distributed-safe (нет sequence contention).

UUIDv7PKMixin в packages/core/src/razmakh_core/models/mixins.py:

class Organization(UUIDv7PKMixin, TimestampsMixin, Base):
    ...
# id Mapped[UUID] с server_default = uuidv7()

Row-Level Security (RLS) — P15 multi-tenant

Context var: `app.organization_id`

H-1 (Opus FINAL #6): name must be app.organization_id (NOT app.org_id — silent bypass risk).

from razmakh_api.db import organization_context

async with get_session() as session, organization_context(session, org_id):
    result = await session.execute(select(MarketplaceAccount))
    # Только rows с organization_id = org_id

Internally: SELECT set_config('app.organization_id', :val, true) (is_local=true = SET LOCAL scope = current transaction).

Policy template

-- core.marketplace_account (P15 RLS scope)
ALTER TABLE core.marketplace_account ENABLE ROW LEVEL SECURITY;
ALTER TABLE core.marketplace_account FORCE ROW LEVEL SECURITY;

CREATE POLICY rls_marketplace_account_organization
ON core.marketplace_account
USING (organization_id = current_setting('app.organization_id', true)::uuid)
WITH CHECK (organization_id = current_setting('app.organization_id', true)::uuid);

USING — что вижу при SELECT/UPDATE/DELETE. WITH CHECK — что могу INSERT/UPDATE (защита от cross-tenant attack).

Default deny

Без set_config() — current_setting() возвращает NULL → cast ::uuid → NULL → id = NULL → all rows excluded → 0 visible. Это безопасный fallback (нет случайного раскрытия).

Verified в integration tests

7/7 pass в apps/api/tests/test_db_integration.py:

test_create_organization_via_app_fails_without_context — INSERT без context blocked
test_uuid_v7_sortable — UUID v7 monotonic
test_rls_organization_self_isolation — org A видит только себя
test_rls_marketplace_account_cross_tenant_probe — INSERT с wrong org_id → ProgrammingError
test_rls_marketplace_account_select_isolation — SELECT через app user filtered
test_rls_no_context_blocks_all — no context → 0 rows
test_set_get_organization_context — round-trip

Baseline tables (Migration 0001_baseline)

core.organization

id              UUID v7 PK, default uuidv7()
slug            varchar(64) UNIQUE NOT NULL
name            varchar(255) NOT NULL
settings        jsonb NOT NULL DEFAULT '{}'
created_at      timestamptz NOT NULL DEFAULT now()
updated_at      timestamptz NOT NULL DEFAULT now() (ON UPDATE trigger)

RLS policy: id = current_setting('app.organization_id')::uuid (self-only)

core.user (NO RLS — SECURITY DEFINER access per razmakh-rls-core-user-2026-05-17.md)

id              UUID v7 PK
organization_id UUID FK core.organization NOT NULL (Phase 1 model — single org per user)
email           citext UNIQUE NOT NULL
display_name    varchar(255)
role            varchar(32) NOT NULL DEFAULT 'owner'
last_login_at   timestamptz
created/updated timestamps

Доступ в Phase 1.5 через app.visible_users(org_id) SECURITY DEFINER function.

core.marketplace_account

id              UUID v7 PK
organization_id UUID FK core.organization NOT NULL
marketplace_code varchar(32) NOT NULL  -- WB / Ozon / Yandex.Market / MVideo
external_id     varchar(128)            -- supplier_id WB, client_id Ozon, etc.
display_name    varchar(255) NOT NULL
fulfillment_type varchar(32) NOT NULL DEFAULT 'FBO'
is_active       bool NOT NULL DEFAULT true
settings        jsonb NOT NULL DEFAULT '{}'
created/updated timestamps

UNIQUE (organization_id, marketplace_code, external_id)
INDEX (organization_id, is_active)

RLS policy: organization_id = current_setting('app.organization_id')::uuid

raw.payload (ETL bronze)

id              UUID v7 (composite PK с collected_at)
organization_id UUID NOT NULL
collector_name  text NOT NULL
collected_at    timestamptz NOT NULL DEFAULT now()
source_url      text
payload         jsonb NOT NULL
payload_hash    text

PARTITION BY RANGE (collected_at) — monthly
INDEX (organization_id, collector_name, collected_at DESC)

RLS enforced. Partitions: raw.payload_y2026m05, raw.payload_y2026m06 (auto-rotated в F-06).

ICU collations (для русского sort)

CREATE COLLATION ru_RU_icu (provider=icu, locale='ru-RU', deterministic=false);  -- CI+AI
CREATE COLLATION ru_RU_icu_cs (provider=icu, locale='ru-RU', deterministic=true); -- CS

Per-column usage (Phase 1+):

CREATE TABLE core.product (
    name varchar(255) COLLATE "ru_RU_icu" NOT NULL,
    ...
);

ON UPDATE trigger

Все *_updated_at columns обновляются через core.tg_set_updated_at() trigger function:

CREATE TRIGGER tg_organization_updated_at
BEFORE UPDATE ON core.organization
FOR EACH ROW EXECUTE FUNCTION core.tg_set_updated_at();

ORM onupdate=datetime.now(UTC) — fallback для тестов когда trigger не applies.

Migrations workflow

Apply (production)

ssh nikolay@razmakh-vps-prod
source ~/.infisical-token.sh
export POSTGRES_PASSWORD=$(infisical secrets get \
  --projectId=... --env=prod --path=/_internal/postgres PASSWORD --plain)
export POSTGRES_HOST=127.0.0.1
export POSTGRES_USER=razmakh_admin  # DDL access
cd /opt/razmakh/repo/apps/api
uv run alembic upgrade head

Generate new

cd apps/api
uv run alembic revision --autogenerate -m "add core.product table"
# Review generated SQL в alembic/versions/XXXX_*.py
uv run alembic upgrade head  # local apply

Bootstrap (one-time)

Alembic нужна core schema для tracking — создается first migration. Для first run на чистой DB:

CREATE SCHEMA IF NOT EXISTS core AUTHORIZATION razmakh_admin;

Затем alembic upgrade head создаёт core.alembic_version + остальное.

Deferred (Phase 1.5+)

core.user SECURITY DEFINER function app.visible_users(org_id) (F-05 или Block 1.5.1)
core.organization_member для multi-org per user (Block 1.5.1)
core.master_sku migration (Block 1.5.7 per H-2)
Auto-partition raw.payload cron (Phase 1 F-06)
pg_exporter dedicated read-only role (F-10)
semgrep rule forbidding raw Session() calls (TBD — currently relies on review)

Sources

Plan: plan/active/00-foundation.md F-04
Decision: razmakh-rls-scope-organization-2026-05-17.md
Decision: razmakh-rls-core-user-2026-05-17.md
Decision: razmakh-rls-context-var-naming-2026-05-17.md (H-1 app.organization_id)
Tests: apps/api/tests/test_db_integration.py (7 pass real PG)