"""PostgreSQL pool and idempotent schema bootstrap.""" import time import psycopg from psycopg.rows import dict_row from psycopg_pool import ConnectionPool MIGRATION_LOCK = 0x4C414234 # "LAB4" SCHEMA = [ """ CREATE TABLE IF NOT EXISTS users ( id UUID PRIMARY KEY, username TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL, password_hash TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL ) """, """ CREATE TABLE IF NOT EXISTS sessions ( token_hash TEXT PRIMARY KEY, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, expires_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ NOT NULL ) """, "CREATE INDEX IF NOT EXISTS sessions_expires_idx ON sessions(expires_at)", """ CREATE TABLE IF NOT EXISTS chats ( id UUID PRIMARY KEY, title TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL ) """, """ CREATE TABLE IF NOT EXISTS chat_members ( chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE, user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, PRIMARY KEY (chat_id, user_id) ) """, "CREATE INDEX IF NOT EXISTS chat_members_user_idx ON chat_members(user_id, chat_id)", """ CREATE TABLE IF NOT EXISTS messages ( id UUID PRIMARY KEY, chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE, sender_id UUID NOT NULL REFERENCES users(id), text TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL ) """, "CREATE INDEX IF NOT EXISTS messages_chat_order_idx ON messages(chat_id, created_at, id)", """ CREATE TABLE IF NOT EXISTS attachments ( id UUID PRIMARY KEY, chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE, owner_id UUID NOT NULL REFERENCES users(id), filename TEXT NOT NULL, content_type TEXT NOT NULL, size_bytes BIGINT NOT NULL, sha256 TEXT NOT NULL, object_key TEXT NOT NULL UNIQUE, status TEXT NOT NULL, variants JSONB NOT NULL DEFAULT '[]'::jsonb, error_code TEXT NULL, created_at TIMESTAMPTZ NOT NULL, updated_at TIMESTAMPTZ NOT NULL ) """, "CREATE INDEX IF NOT EXISTS attachments_chat_idx ON attachments(chat_id)", """ CREATE TABLE IF NOT EXISTS message_attachments ( message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE, attachment_id UUID NOT NULL REFERENCES attachments(id), position INTEGER NOT NULL, PRIMARY KEY (message_id, attachment_id), UNIQUE (message_id, position) ) """, "CREATE INDEX IF NOT EXISTS message_attachments_attachment_idx ON message_attachments(attachment_id)", """ CREATE TABLE IF NOT EXISTS idempotency ( user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, chat_id UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE, key TEXT NOT NULL, request_hash TEXT NOT NULL, message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL, PRIMARY KEY (user_id, chat_id, key) ) """, "CREATE INDEX IF NOT EXISTS idempotency_created_idx ON idempotency(created_at)", ] def wait_for_database(dsn, timeout_seconds=90): deadline = time.monotonic() + timeout_seconds last_error = None while time.monotonic() < deadline: try: with psycopg.connect(dsn, connect_timeout=3) as connection: connection.execute("SELECT 1") return except psycopg.Error as error: last_error = error time.sleep(1) raise RuntimeError("PostgreSQL did not become available") from last_error def create_pool(dsn): return ConnectionPool( conninfo=dsn, min_size=1, max_size=10, timeout=10, kwargs={"row_factory": dict_row}, open=False, ) def migrate(pool): with pool.connection() as connection: with connection.transaction(): with connection.cursor() as cursor: cursor.execute("SELECT pg_advisory_xact_lock(%s)", (MIGRATION_LOCK,)) for statement in SCHEMA: cursor.execute(statement)