Files
mtusi2026lab5/db.py
2026-11-19 19:45:26 +03:00

699 lines
31 KiB
Python

import hashlib
import json
import os
import uuid
from pathlib import Path
from psycopg import errors
from psycopg.rows import dict_row
from psycopg_pool import ConnectionPool, PoolTimeout
from psycopg.types.json import Jsonb
MIGRATIONS_DIR = Path(__file__).resolve().parent / 'migrations'
class ApiError(Exception):
def __init__(self, status, code, message):
super().__init__(message)
self.status = status
self.code = code
self.message = message
def read_database_url():
path = os.environ.get('DATABASE_URL_FILE')
if path and os.path.isfile(path):
return Path(path).read_text(encoding='utf-8').strip()
url = os.environ.get('DATABASE_URL', '').strip()
if not url:
raise RuntimeError('DATABASE_URL or DATABASE_URL_FILE is required')
return url
class Store:
def __init__(self, pool: ConnectionPool):
self.pool = pool
@classmethod
def connect(cls, run_migrations=True):
pool = ConnectionPool(
conninfo=read_database_url(),
min_size=1,
max_size=10,
timeout=5.0,
kwargs={
'row_factory': dict_row,
'options': '-c statement_timeout=5000 -c lock_timeout=2000',
},
open=True,
)
store = cls(pool)
if run_migrations:
store.run_migrations()
return store
def close(self):
self.pool.close()
def ping(self):
with self.pool.connection() as conn:
conn.execute('SELECT 1')
def run_migrations(self):
with self.pool.connection() as conn:
conn.execute(
'CREATE TABLE IF NOT EXISTS schema_migrations '
'(version TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW())'
)
for path in sorted(MIGRATIONS_DIR.glob('*.sql')):
version = path.name
row = conn.execute(
'SELECT 1 FROM schema_migrations WHERE version = %s',
(version,),
).fetchone()
if row:
continue
sql = path.read_text(encoding='utf-8')
with conn.transaction():
conn.execute(sql)
conn.execute(
'INSERT INTO schema_migrations (version) VALUES (%s)',
(version,),
)
@staticmethod
def _user_row(row):
return {
'id': str(row['id']),
'username': row['username'],
'display_name': row['display_name'],
'created_at': row['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
}
def register_user(self, username, display_name, password_hash, created_at):
user_id = str(uuid.uuid4())
try:
with self.pool.connection() as conn:
conn.execute(
'INSERT INTO users (id, username, display_name, password_hash, created_at) '
'VALUES (%s, %s, %s, %s, %s)',
(user_id, username, display_name, password_hash, created_at),
)
except errors.UniqueViolation:
raise ApiError(409, 'conflict', 'Username already exists') from None
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
return {
'id': user_id,
'username': username,
'display_name': display_name,
'created_at': created_at,
}
def get_user(self, user_id):
with self.pool.connection() as conn:
row = conn.execute(
'SELECT id, username, display_name, created_at FROM users WHERE id = %s',
(user_id,),
).fetchone()
if row is None:
return None
return self._user_row(row)
def get_user_with_password(self, username):
with self.pool.connection() as conn:
row = conn.execute(
'SELECT id, username, display_name, password_hash, created_at FROM users WHERE username = %s',
(username,),
).fetchone()
return row
def create_chat(self, user_id, title, member_ids, created_at):
chat_id = str(uuid.uuid4())
try:
with self.pool.connection() as conn:
with conn.transaction():
rows = conn.execute(
'SELECT id FROM users WHERE id = ANY(%s)',
(member_ids,),
).fetchall()
if len(rows) != len(set(member_ids)):
raise ApiError(400, 'invalid_request', 'Unknown chat member')
conn.execute(
'INSERT INTO chats (id, title, created_at) VALUES (%s, %s, %s)',
(chat_id, title, created_at),
)
for index, member_id in enumerate(member_ids):
conn.execute(
'INSERT INTO chat_members (chat_id, user_id, position) VALUES (%s, %s, %s)',
(chat_id, member_id, index),
)
except ApiError:
raise
except errors.Error:
raise ApiError(500, 'internal_error', 'Internal server error') from None
return {
'id': chat_id,
'title': title,
'member_ids': list(member_ids),
'created_at': created_at,
}
def _is_member(self, conn, chat_id, user_id):
row = conn.execute(
'SELECT 1 FROM chat_members WHERE chat_id = %s AND user_id = %s',
(chat_id, user_id),
).fetchone()
return row is not None
def get_chat(self, user_id, chat_id):
with self.pool.connection() as conn:
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
chat = conn.execute(
'SELECT id, title, created_at FROM chats WHERE id = %s',
(chat_id,),
).fetchone()
members = conn.execute(
'SELECT user_id FROM chat_members WHERE chat_id = %s ORDER BY position',
(chat_id,),
).fetchall()
return {
'id': str(chat['id']),
'title': chat['title'],
'member_ids': [str(row['user_id']) for row in members],
'created_at': chat['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
}
def list_chats(self, user_id, limit, cursor_key):
query = (
'SELECT c.id, c.title, c.created_at '
'FROM chats c '
'JOIN chat_members cm ON cm.chat_id = c.id AND cm.user_id = %s '
)
params = [user_id]
if cursor_key:
query += 'WHERE (c.created_at, c.id) > (%s, %s) '
params.extend(cursor_key)
query += 'ORDER BY c.created_at ASC, c.id ASC LIMIT %s'
params.append(limit + 1)
with self.pool.connection() as conn:
rows = conn.execute(query, params).fetchall()
has_more = len(rows) > limit
page = rows[:limit]
items = []
for row in page:
chat_id = str(row['id'])
items.append(self.get_chat(user_id, chat_id))
next_cursor = None
if has_more:
last = page[-1]
next_cursor = (last['created_at'], str(last['id']))
return items, next_cursor
def create_message(self, user_id, chat_id, text, created_at):
message_id = str(uuid.uuid4())
with self.pool.connection() as conn:
with conn.transaction():
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
conn.execute(
'INSERT INTO messages (id, chat_id, sender_id, text, created_at) '
'VALUES (%s, %s, %s, %s, %s)',
(message_id, chat_id, user_id, text, created_at),
)
return {
'id': message_id,
'chat_id': chat_id,
'sender_id': user_id,
'text': text,
'created_at': created_at,
'attachment_ids': [],
}
def create_message_idempotent(
self, user_id, chat_id, text, attachment_ids, idempotency_key, created_at
):
resource_path = '/api/v1/chats/' + chat_id + '/messages'
request_hash = hashlib.sha256(
json.dumps(
{
'chat_id': chat_id,
'text': text,
'attachment_ids': attachment_ids,
},
ensure_ascii=False,
sort_keys=True,
separators=(',', ':'),
).encode('utf-8')
).hexdigest()
lock_key = '\x1f'.join(
(user_id, 'POST', resource_path, idempotency_key)
)
try:
with self.pool.connection() as conn:
with conn.transaction():
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
conn.execute(
'SELECT pg_advisory_xact_lock(hashtextextended(%s, 0))',
(lock_key,),
)
row = conn.execute(
'SELECT request_hash, response FROM message_idempotency '
'WHERE user_id = %s AND method = %s AND resource_path = %s '
'AND idempotency_key = %s AND expires_at > NOW() FOR UPDATE',
(user_id, 'POST', resource_path, idempotency_key),
).fetchone()
if row is not None:
if row['request_hash'] != request_hash:
raise ApiError(409, 'conflict', 'Idempotency-Key was used with a different request')
return row['response']
conn.execute(
'DELETE FROM message_idempotency WHERE user_id = %s '
'AND method = %s AND resource_path = %s AND idempotency_key = %s',
(user_id, 'POST', resource_path, idempotency_key),
)
conn.execute(
'DELETE FROM message_idempotency WHERE user_id = %s AND expires_at <= NOW()',
(user_id,),
)
if attachment_ids:
attachments = conn.execute(
'SELECT id, status FROM attachments '
'WHERE chat_id = %s AND id = ANY(%s) FOR SHARE',
(chat_id, attachment_ids),
).fetchall()
if len(attachments) != len(attachment_ids):
raise ApiError(404, 'not_found', 'Attachment not found')
if any(row['status'] == 'failed' for row in attachments):
raise ApiError(409, 'conflict', 'Attachment is not ready')
conn.execute(
'INSERT INTO message_idempotency '
'(user_id, method, resource_path, idempotency_key, request_hash, expires_at) '
"VALUES (%s, %s, %s, %s, %s, NOW() + INTERVAL '24 hours')",
(user_id, 'POST', resource_path, idempotency_key, request_hash),
)
message_id = str(uuid.uuid4())
conn.execute(
'INSERT INTO messages (id, chat_id, sender_id, text, created_at) '
'VALUES (%s, %s, %s, %s, %s)',
(message_id, chat_id, user_id, text, created_at),
)
for position, attachment_id in enumerate(attachment_ids):
conn.execute(
'INSERT INTO message_attachments '
'(message_id, attachment_id, position) VALUES (%s, %s, %s)',
(message_id, attachment_id, position),
)
message = {
'id': message_id,
'chat_id': chat_id,
'sender_id': user_id,
'text': text,
'created_at': created_at,
'attachment_ids': list(attachment_ids),
}
conn.execute(
'UPDATE message_idempotency SET response = %s '
'WHERE user_id = %s AND method = %s AND resource_path = %s '
'AND idempotency_key = %s',
(Jsonb(message), user_id, 'POST', resource_path, idempotency_key),
)
return message
except ApiError:
raise
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def list_messages(self, user_id, chat_id, limit, cursor_key):
with self.pool.connection() as conn:
if not self._is_member(conn, chat_id, user_id):
raise ApiError(404, 'not_found', 'Chat not found')
query = (
'SELECT m.id, m.chat_id, m.sender_id, m.text, m.created_at, '
'COALESCE(array_agg(ma.attachment_id ORDER BY ma.position) '
'FILTER (WHERE ma.attachment_id IS NOT NULL), ARRAY[]::UUID[]) AS attachment_ids '
'FROM messages m LEFT JOIN message_attachments ma ON ma.message_id = m.id '
'WHERE m.chat_id = %s '
)
params = [chat_id]
if cursor_key:
query += 'AND (m.created_at, m.id) > (%s, %s) '
params.extend(cursor_key)
query += (
'GROUP BY m.id '
'ORDER BY m.created_at ASC, m.id ASC LIMIT %s'
)
params.append(limit + 1)
with self.pool.connection() as conn:
rows = conn.execute(query, params).fetchall()
has_more = len(rows) > limit
page = rows[:limit]
items = [
{
'id': str(row['id']),
'chat_id': str(row['chat_id']),
'sender_id': str(row['sender_id']),
'text': row['text'],
'created_at': row['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
'attachment_ids': [str(value) for value in row['attachment_ids']],
}
for row in page
]
next_cursor = None
if has_more:
last = page[-1]
next_cursor = (last['created_at'], str(last['id']))
return items, next_cursor
@staticmethod
def _attachment_row(row, include_key=False):
value = {
'id': str(row['id']),
'chat_id': str(row['chat_id']),
'owner_id': str(row['owner_id']),
'filename': row['filename'],
'content_type': row['content_type'],
'size_bytes': row['size_bytes'],
'sha256': row['sha256'].strip(),
'status': row['status'],
'variants': row['variants'],
'error_code': row['error_code'],
'created_at': row['created_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
'updated_at': row['updated_at'].isoformat(timespec='microseconds').replace('+00:00', 'Z'),
}
if include_key:
value['object_key'] = row['object_key']
value['staging_key'] = row['staging_key']
value['width'] = row['width']
value['height'] = row['height']
return value
def create_attachment_upload(
self, attachment_id, chat_id, owner_id, object_key, filename,
staging_key, content_type, size_bytes, sha256, width, height,
job_id, created_at
):
try:
with self.pool.connection() as conn:
with conn.transaction():
if not self._is_member(conn, chat_id, owner_id):
raise ApiError(404, 'not_found', 'Chat not found')
conn.execute(
'INSERT INTO attachments '
'(id, chat_id, owner_id, object_key, staging_key, filename, content_type, '
'size_bytes, sha256, width, height, status, variants, error_code, '
'pipeline_version, created_at, updated_at) '
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, "
"'queued', '[]'::jsonb, NULL, 1, %s, %s)",
(
attachment_id, chat_id, owner_id, object_key, staging_key, filename,
content_type, size_bytes, sha256, width, height,
created_at, created_at,
),
)
conn.execute(
'INSERT INTO attachment_jobs '
'(job_id, attachment_id, pipeline_version, status, created_at, updated_at) '
"VALUES (%s, %s, 1, 'queued', %s, %s)",
(job_id, attachment_id, created_at, created_at),
)
conn.execute(
'INSERT INTO attachment_outbox (job_id, created_at) VALUES (%s, %s)',
(job_id, created_at),
)
except ApiError:
raise
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def mark_attachment_ready(self, attachment_id, updated_at):
try:
with self.pool.connection() as conn:
with conn.transaction():
row = conn.execute(
'UPDATE attachments SET status = %s, error_code = NULL, updated_at = %s '
"WHERE id = %s AND status IN ('queued', 'processing') RETURNING *",
('ready', updated_at, attachment_id),
).fetchone()
if row is None:
current = conn.execute(
'SELECT * FROM attachments WHERE id = %s', (attachment_id,)
).fetchone()
if current and current['status'] == 'ready':
return self._attachment_row(current)
raise ApiError(503, 'unavailable', 'Attachment state could not be saved')
conn.execute(
"UPDATE attachment_jobs SET status = 'ready', lease_until = NULL, updated_at = %s "
'WHERE attachment_id = %s',
(updated_at, attachment_id),
)
return self._attachment_row(row)
except ApiError:
raise
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def mark_attachment_failed(self, attachment_id, error_code, updated_at):
try:
with self.pool.connection() as conn:
conn.execute(
'UPDATE attachments SET status = %s, error_code = %s, updated_at = %s '
"WHERE id = %s AND status IN ('queued', 'processing')",
('failed', error_code, updated_at, attachment_id),
)
conn.execute(
"UPDATE attachment_jobs SET status = 'failed', lease_until = NULL, "
'last_error = %s, updated_at = %s WHERE attachment_id = %s',
(error_code, updated_at, attachment_id),
)
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def claim_job(self, job_id, lease_until, updated_at):
try:
with self.pool.connection() as conn:
with conn.transaction():
row = conn.execute(
"UPDATE attachment_jobs SET status = 'processing', attempts = attempts + 1, "
'lease_until = %s, updated_at = %s '
"WHERE job_id = %s AND status IN ('queued', 'processing') "
'AND pipeline_version = (SELECT pipeline_version FROM attachments '
'WHERE id = attachment_jobs.attachment_id) '
"AND (lease_until IS NULL OR lease_until < NOW()) "
'RETURNING job_id, attachment_id, pipeline_version, attempts',
(lease_until, updated_at, job_id),
).fetchone()
if row is None:
return None
conn.execute(
"UPDATE attachments SET status = 'processing', updated_at = %s "
"WHERE id = %s AND status = 'queued'",
(updated_at, row['attachment_id']),
)
return row
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def get_attachment_for_job(self, attachment_id):
with self.pool.connection() as conn:
row = conn.execute(
'SELECT * FROM attachments WHERE id = %s', (attachment_id,)
).fetchone()
return self._attachment_row(row, include_key=True) if row else None
def set_attachment_variants(self, attachment_id, variants, updated_at):
try:
with self.pool.connection() as conn:
with conn.transaction():
row = conn.execute(
'UPDATE attachments SET status = %s, variants = %s, error_code = NULL, '
'updated_at = %s WHERE id = %s AND status IN (%s, %s) RETURNING *',
('ready', Jsonb(variants), updated_at, attachment_id, 'queued', 'processing'),
).fetchone()
if row is None:
current = conn.execute(
'SELECT * FROM attachments WHERE id = %s', (attachment_id,)
).fetchone()
return self._attachment_row(current) if current else None
conn.execute(
"UPDATE attachment_jobs SET status = 'ready', lease_until = NULL, "
'updated_at = %s WHERE attachment_id = %s',
(updated_at, attachment_id),
)
return self._attachment_row(row)
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def retry_job(self, job_id, attachment_id, next_attempt_at, error_code, updated_at):
try:
with self.pool.connection() as conn:
with conn.transaction():
conn.execute(
"UPDATE attachment_jobs SET status = 'queued', lease_until = NULL, "
'next_attempt_at = %s, last_error = %s, updated_at = %s WHERE job_id = %s',
(next_attempt_at, error_code, updated_at, job_id),
)
conn.execute(
"UPDATE attachments SET status = 'queued', error_code = %s, updated_at = %s "
"WHERE id = %s AND status = 'processing'",
(error_code, updated_at, attachment_id),
)
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def fail_job(self, job_id, attachment_id, error_code, updated_at):
self.mark_attachment_failed(attachment_id, error_code, updated_at)
def pending_outbox(self, limit=100):
with self.pool.connection() as conn:
rows = conn.execute(
'SELECT o.id, o.job_id, j.attachment_id, j.pipeline_version '
'FROM attachment_outbox o JOIN attachment_jobs j ON j.job_id = o.job_id '
'WHERE o.published_at IS NULL ORDER BY o.id LIMIT %s',
(limit,),
).fetchall()
return rows
def mark_outbox_published(self, outbox_id, published_at):
try:
with self.pool.connection() as conn:
conn.execute(
'UPDATE attachment_outbox SET published_at = %s '
'WHERE id = %s AND published_at IS NULL',
(published_at, outbox_id),
)
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def replay_attachment(self, attachment_id, reason, created_at):
job_id = str(uuid.uuid4())
try:
with self.pool.connection() as conn:
with conn.transaction():
row = conn.execute(
"SELECT pipeline_version, status FROM attachments WHERE id = %s FOR UPDATE",
(attachment_id,),
).fetchone()
if row is None:
raise ApiError(404, 'not_found', 'Attachment not found')
if row['status'] != 'failed':
raise ApiError(409, 'conflict', 'Only failed attachments can be replayed')
old_version = row['pipeline_version']
new_version = old_version + 1
conn.execute(
"UPDATE attachments SET status = 'queued', error_code = NULL, "
"variants = '[]'::jsonb, pipeline_version = %s, updated_at = %s "
"WHERE id = %s",
(new_version, created_at, attachment_id),
)
conn.execute(
"INSERT INTO attachment_jobs "
"(job_id, attachment_id, pipeline_version, status, created_at, updated_at) "
"VALUES (%s, %s, %s, 'queued', %s, %s)",
(job_id, attachment_id, new_version, created_at, created_at),
)
conn.execute(
"INSERT INTO attachment_outbox (job_id, created_at) VALUES (%s, %s)",
(job_id, created_at),
)
conn.execute(
"INSERT INTO attachment_replay_audit "
"(attachment_id, old_pipeline_version, new_pipeline_version, reason, created_at) "
"VALUES (%s, %s, %s, %s, %s)",
(attachment_id, old_version, new_version, reason, created_at),
)
return job_id, new_version
except ApiError:
raise
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
def get_attachment(self, user_id, attachment_id):
try:
with self.pool.connection() as conn:
row = conn.execute(
'SELECT a.* FROM attachments a '
'JOIN chat_members cm ON cm.chat_id = a.chat_id '
'WHERE a.id = %s AND cm.user_id = %s',
(attachment_id, user_id),
).fetchone()
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
if row is None:
raise ApiError(404, 'not_found', 'Attachment not found')
return self._attachment_row(row)
def get_attachment_for_download(self, user_id, attachment_id):
try:
with self.pool.connection() as conn:
row = conn.execute(
'SELECT a.* FROM attachments a '
'JOIN chat_members cm ON cm.chat_id = a.chat_id '
'WHERE a.id = %s AND cm.user_id = %s',
(attachment_id, user_id),
).fetchone()
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
if row is None:
raise ApiError(404, 'not_found', 'Attachment not found')
return self._attachment_row(row, include_key=True)
def list_stale_processing_attachments(self, cutoff):
try:
with self.pool.connection() as conn:
rows = conn.execute(
"SELECT * FROM attachments WHERE status = 'processing' AND created_at < %s "
'ORDER BY created_at, id',
(cutoff,),
).fetchall()
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
return [self._attachment_row(row, include_key=True) for row in rows]
def list_ready_attachments(self):
try:
with self.pool.connection() as conn:
rows = conn.execute(
"SELECT * FROM attachments WHERE status = 'ready' ORDER BY id"
).fetchall()
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
return [self._attachment_row(row, include_key=True) for row in rows]
def list_attachments_for_reconciliation(self):
try:
with self.pool.connection() as conn:
rows = conn.execute(
'SELECT * FROM attachments ORDER BY id'
).fetchall()
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
return [self._attachment_row(row, include_key=True) for row in rows]
def get_attachment_by_object_key(self, object_key):
try:
with self.pool.connection() as conn:
row = conn.execute(
'SELECT * FROM attachments WHERE object_key = %s',
(object_key,),
).fetchone()
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None
if row is None:
return None
return self._attachment_row(row, include_key=True)
def mark_reconciled_missing(self, attachment_id, updated_at):
try:
with self.pool.connection() as conn:
conn.execute(
'UPDATE attachments SET status = %s, error_code = %s, updated_at = %s '
"WHERE id = %s AND status = 'ready'",
('failed', 'object_missing', updated_at, attachment_id),
)
except (errors.Error, PoolTimeout):
raise ApiError(503, 'unavailable', 'Service unavailable') from None