forked from mtusi2026/mtusi2026lab5
511 lines
23 KiB
Python
511 lines
23 KiB
Python
import asyncio
|
|
import hashlib
|
|
import json
|
|
import uuid
|
|
from datetime import datetime, timezone, timedelta
|
|
from typing import Optional
|
|
|
|
import asyncpg
|
|
|
|
from app.cache import Cache
|
|
|
|
|
|
def _now_iso(value: datetime) -> str:
|
|
if value.tzinfo is None:
|
|
value = value.replace(tzinfo=timezone.utc)
|
|
return value.astimezone(timezone.utc).isoformat().replace("+00:00", "Z")
|
|
|
|
|
|
def _canonical_hash(payload: dict) -> str:
|
|
canonical = json.dumps(payload, sort_keys=True, separators=(",", ":"))
|
|
return hashlib.sha256(canonical.encode("utf-8")).hexdigest()
|
|
|
|
|
|
class Storage:
|
|
def __init__(self, pool: asyncpg.Pool, cache: Cache) -> None:
|
|
self._pool = pool
|
|
self._cache = cache
|
|
|
|
async def create_user(self, username, display_name, password_hash) -> dict:
|
|
user_id = str(uuid.uuid4())
|
|
try:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"""INSERT INTO users (id, username, display_name, password_hash)
|
|
VALUES ($1,$2,$3,$4)
|
|
RETURNING id, username, display_name, created_at""",
|
|
user_id, username, display_name, password_hash,
|
|
)
|
|
except asyncpg.UniqueViolationError:
|
|
raise ValueError("conflict")
|
|
return self._user_row(row)
|
|
|
|
async def get_user(self, user_id: str) -> Optional[dict]:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"SELECT id, username, display_name, created_at FROM users WHERE id=$1",
|
|
user_id,
|
|
)
|
|
return self._user_row(row) if row else None
|
|
|
|
async def get_user_by_username(self, username: str) -> Optional[dict]:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"SELECT id, username, display_name, password_hash, created_at FROM users WHERE username=$1",
|
|
username,
|
|
)
|
|
return dict(row) if row else None
|
|
|
|
async def create_chat(self, title, member_ids, creator_id) -> dict:
|
|
if creator_id not in member_ids:
|
|
raise ValueError("creator_not_member")
|
|
chat_id = str(uuid.uuid4())
|
|
async with self._pool.acquire() as conn:
|
|
async with conn.transaction():
|
|
existing = await conn.fetchval(
|
|
"SELECT count(*) FROM users WHERE id = ANY($1::uuid[])", member_ids,
|
|
)
|
|
if existing != len(member_ids):
|
|
raise ValueError("invalid_member")
|
|
await conn.execute("INSERT INTO chats (id, title) VALUES ($1,$2)", chat_id, title)
|
|
await conn.executemany(
|
|
"INSERT INTO chat_members (chat_id, user_id) VALUES ($1,$2)",
|
|
[(chat_id, uid) for uid in member_ids],
|
|
)
|
|
created = await conn.fetchval("SELECT created_at FROM chats WHERE id=$1", chat_id)
|
|
return {"id": chat_id, "title": title, "member_ids": member_ids,
|
|
"created_at": _now_iso(created)}
|
|
|
|
async def get_chat(self, chat_id: str, actor_id: str) -> Optional[dict]:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"SELECT id, title, created_at FROM chats WHERE id=$1", chat_id,
|
|
)
|
|
if row is None:
|
|
return None
|
|
members = await conn.fetch("SELECT user_id FROM chat_members WHERE chat_id=$1", chat_id)
|
|
member_ids = [str(m["user_id"]) for m in members]
|
|
if actor_id not in member_ids:
|
|
return None
|
|
return {"id": str(row["id"]), "title": row["title"],
|
|
"member_ids": member_ids, "created_at": _now_iso(row["created_at"])}
|
|
|
|
async def list_chats(self, actor_id, limit, cursor):
|
|
async with self._pool.acquire() as conn:
|
|
params = [actor_id]
|
|
where = ""
|
|
if cursor:
|
|
try:
|
|
cur_actor, cur_created, cur_id = cursor.split("|", 2)
|
|
except ValueError:
|
|
raise ValueError("bad_cursor")
|
|
if cur_actor != actor_id:
|
|
raise ValueError("bad_cursor")
|
|
where = "AND (c.created_at, c.id) > ($2,$3)"
|
|
params += [datetime.fromisoformat(cur_created.replace("Z", "+00:00")), cur_id]
|
|
rows = await conn.fetch(
|
|
f"""SELECT c.id, c.title, c.created_at
|
|
FROM chats c JOIN chat_members m ON m.chat_id=c.id
|
|
WHERE m.user_id=$1 {where}
|
|
ORDER BY c.created_at, c.id LIMIT ${len(params)+1}""",
|
|
*params, limit + 1,
|
|
)
|
|
items = []
|
|
for row in rows[:limit]:
|
|
members = await conn.fetch("SELECT user_id FROM chat_members WHERE chat_id=$1", row["id"])
|
|
items.append({"id": str(row["id"]), "title": row["title"],
|
|
"member_ids": [str(m["user_id"]) for m in members],
|
|
"created_at": _now_iso(row["created_at"])})
|
|
next_cursor = None
|
|
if len(rows) > limit:
|
|
last = rows[limit - 1]
|
|
next_cursor = f"{actor_id}|{_now_iso(last['created_at'])}|{last['id']}"
|
|
return items, next_cursor
|
|
|
|
async def is_chat_member(self, chat_id: str, user_id: str) -> bool:
|
|
async with self._pool.acquire() as conn:
|
|
return await conn.fetchval(
|
|
"SELECT 1 FROM chat_members WHERE chat_id=$1 AND user_id=$2",
|
|
chat_id, user_id,
|
|
) is not None
|
|
|
|
async def create_pending_attachment(self, att_id, chat_id, owner_id, object_key,
|
|
filename, content_type, size_bytes, sha256):
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"""INSERT INTO attachments
|
|
(id, chat_id, owner_id, object_key, filename, content_type,
|
|
size_bytes, sha256, status)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'queued')
|
|
RETURNING id, chat_id, owner_id, object_key, filename,
|
|
content_type, size_bytes, sha256, status,
|
|
variants, variant_keys, error_code, created_at, updated_at""",
|
|
att_id, chat_id, owner_id, object_key,
|
|
filename, content_type, size_bytes, sha256,
|
|
)
|
|
return self._attachment_row(row, public=True)
|
|
|
|
async def create_attachment_with_job_and_outbox(
|
|
self, att_id, chat_id, owner_id, object_key, filename, content_type,
|
|
size_bytes, sha256, job: dict,
|
|
):
|
|
async with self._pool.acquire() as conn:
|
|
async with conn.transaction():
|
|
row = await conn.fetchrow(
|
|
"""INSERT INTO attachments
|
|
(id, chat_id, owner_id, object_key, filename, content_type,
|
|
size_bytes, sha256, status)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'queued')
|
|
RETURNING id, chat_id, owner_id, object_key, filename,
|
|
content_type, size_bytes, sha256, status,
|
|
variants, variant_keys, error_code, created_at, updated_at""",
|
|
att_id, chat_id, owner_id, object_key,
|
|
filename, content_type, size_bytes, sha256,
|
|
)
|
|
await conn.execute(
|
|
"""INSERT INTO jobs (id, attachment_id, pipeline_version, attempt, status)
|
|
VALUES ($1,$2,$3,$4,'queued')""",
|
|
job["job_id"], job["attachment_id"], job["pipeline_version"], job["attempt"],
|
|
)
|
|
await conn.execute(
|
|
"INSERT INTO outbox (id, job_id, payload) VALUES ($1, $2, $3)",
|
|
str(uuid.uuid4()), job["job_id"], json.dumps(job),
|
|
)
|
|
return self._attachment_row(row, public=True)
|
|
|
|
async def get_attachment(self, att_id: str) -> Optional[dict]:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"""SELECT id, chat_id, owner_id, object_key, filename,
|
|
content_type, size_bytes, sha256, status,
|
|
variants, variant_keys, error_code, created_at, updated_at
|
|
FROM attachments WHERE id=$1""",
|
|
att_id,
|
|
)
|
|
if row is None:
|
|
return None
|
|
return self._attachment_row(row, public=False)
|
|
|
|
async def update_attachment_status(self, att_id: str, status: str) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"UPDATE attachments SET status=$2, updated_at=now() WHERE id=$1",
|
|
att_id, status,
|
|
)
|
|
|
|
async def complete_attachment_ready(self, att_id, original_key, variants: list, variant_keys: dict):
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"""UPDATE attachments SET status='ready', object_key=$2,
|
|
variants=$3::jsonb, variant_keys=$4::jsonb, updated_at=now() WHERE id=$1""",
|
|
att_id, original_key, json.dumps(variants), json.dumps(variant_keys),
|
|
)
|
|
|
|
async def fail_attachment(self, att_id: str, error_code: str) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"UPDATE attachments SET status='failed', error_code=$2, updated_at=now() WHERE id=$1",
|
|
att_id, error_code,
|
|
)
|
|
|
|
async def replay_failed_attachment(self, att_id: str) -> str:
|
|
async with self._pool.acquire() as conn:
|
|
async with conn.transaction():
|
|
row = await conn.fetchrow(
|
|
"SELECT id, status FROM attachments WHERE id=$1 FOR UPDATE",
|
|
att_id,
|
|
)
|
|
if row is None:
|
|
return "not_found"
|
|
if row["status"] == "ready":
|
|
return "already_ready"
|
|
if row["status"] != "failed":
|
|
return "not_failed"
|
|
job_id = str(uuid.uuid4())
|
|
job = {
|
|
"schema_version": 1,
|
|
"job_id": job_id,
|
|
"type": "attachment.process.v1",
|
|
"attachment_id": att_id,
|
|
"pipeline_version": 1,
|
|
"attempt": 1,
|
|
"created_at": datetime.now(timezone.utc).isoformat().replace("+00:00", "Z"),
|
|
}
|
|
await conn.execute(
|
|
"UPDATE attachments SET status='queued', error_code=NULL, updated_at=now() WHERE id=$1",
|
|
att_id,
|
|
)
|
|
await conn.execute(
|
|
"""INSERT INTO jobs (id, attachment_id, pipeline_version, attempt, status)
|
|
VALUES ($1,$2,$3,$4,'queued')""",
|
|
job_id, att_id, 1, 1,
|
|
)
|
|
await conn.execute(
|
|
"INSERT INTO outbox (id, job_id, payload) VALUES ($1, $2, $3)",
|
|
str(uuid.uuid4()), job_id, json.dumps(job),
|
|
)
|
|
return "queued"
|
|
|
|
async def create_job(self, job: dict) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"""INSERT INTO jobs (id, attachment_id, pipeline_version, attempt, status)
|
|
VALUES ($1,$2,$3,$4,'queued')""",
|
|
job["job_id"], job["attachment_id"], job["pipeline_version"], job["attempt"],
|
|
)
|
|
|
|
async def increment_job_attempt(self, job_id: str) -> int:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"UPDATE jobs SET attempt=attempt+1, updated_at=now() WHERE id=$1 RETURNING attempt",
|
|
job_id,
|
|
)
|
|
return int(row["attempt"]) if row is not None else 0
|
|
|
|
async def set_job_lease(self, job_id: str, seconds: int = 60) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"UPDATE jobs SET lease_until = now() + interval '1 second' * $2 WHERE id = $1",
|
|
job_id, seconds,
|
|
)
|
|
|
|
async def get_expired_jobs(self) -> list[dict]:
|
|
async with self._pool.acquire() as conn:
|
|
rows = await conn.fetch(
|
|
"""SELECT j.id as job_id, j.attachment_id, j.pipeline_version, j.attempt, j.status, a.chat_id
|
|
FROM jobs j
|
|
JOIN attachments a ON a.id = j.attachment_id
|
|
WHERE j.status = 'processing' AND j.lease_until < now()"""
|
|
)
|
|
return [dict(r) for r in rows]
|
|
|
|
async def reset_job_status(self, job_id: str) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"UPDATE jobs SET status='queued', lease_until=NULL WHERE id=$1",
|
|
job_id,
|
|
)
|
|
|
|
async def create_outbox(self, job: dict) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"""INSERT INTO outbox (id, job_id, payload) VALUES ($1, $2, $3)""",
|
|
str(uuid.uuid4()), job["job_id"], json.dumps(job),
|
|
)
|
|
|
|
async def get_unpublished_outbox(self, limit: int = 10) -> list[dict]:
|
|
async with self._pool.acquire() as conn:
|
|
rows = await conn.fetch(
|
|
"SELECT id, job_id, payload FROM outbox WHERE published_at IS NULL ORDER BY created_at LIMIT $1",
|
|
limit,
|
|
)
|
|
return [dict(r) for r in rows]
|
|
|
|
async def mark_outbox_published(self, outbox_id: str) -> None:
|
|
async with self._pool.acquire() as conn:
|
|
await conn.execute(
|
|
"UPDATE outbox SET published_at = now() WHERE id = $1",
|
|
outbox_id,
|
|
)
|
|
|
|
async def _list_attachment_ids_conn(self, conn, message_id: str) -> list[str]:
|
|
rows = await conn.fetch(
|
|
"SELECT attachment_id FROM message_attachments WHERE message_id=$1", message_id,
|
|
)
|
|
return [str(r["attachment_id"]) for r in rows]
|
|
|
|
async def add_message(self, chat_id, sender_id, text, attachment_ids=None,
|
|
idempotency_key=None):
|
|
attachment_ids = attachment_ids or []
|
|
request_hash = _canonical_hash({"text": text, "attachment_ids": sorted(attachment_ids)})
|
|
path = f"/api/v1/chats/{chat_id}/messages"
|
|
method = "POST"
|
|
|
|
if idempotency_key is not None:
|
|
cached = await self._get_idempotent(sender_id, method, path, idempotency_key, request_hash)
|
|
if cached is not None:
|
|
return cached
|
|
|
|
try:
|
|
async with self._pool.acquire() as conn:
|
|
async with conn.transaction():
|
|
if attachment_ids:
|
|
valid = await conn.fetchval(
|
|
"""SELECT count(*) FROM attachments
|
|
WHERE id = ANY($1::uuid[]) AND chat_id=$2""",
|
|
attachment_ids, chat_id,
|
|
)
|
|
if valid != len(attachment_ids):
|
|
raise ValueError("not_found")
|
|
failed = await conn.fetchval(
|
|
"""SELECT count(*) FROM attachments
|
|
WHERE id = ANY($1::uuid[]) AND chat_id=$2 AND status='failed'""",
|
|
attachment_ids, chat_id,
|
|
)
|
|
if failed:
|
|
raise ValueError("attachment_failed")
|
|
|
|
message_id = str(uuid.uuid4())
|
|
row = await conn.fetchrow(
|
|
"""INSERT INTO messages (id, chat_id, sender_id, text)
|
|
SELECT $1,$2,$3,$4 WHERE EXISTS (
|
|
SELECT 1 FROM chat_members WHERE chat_id=$2 AND user_id=$3)
|
|
RETURNING id, chat_id, sender_id, text, created_at""",
|
|
message_id, chat_id, sender_id, text,
|
|
)
|
|
if row is None:
|
|
raise ValueError("not_found")
|
|
if attachment_ids:
|
|
await conn.executemany(
|
|
"INSERT INTO message_attachments (message_id, attachment_id) VALUES ($1,$2)",
|
|
[(message_id, aid) for aid in attachment_ids],
|
|
)
|
|
message = self._message_row(row, list(attachment_ids))
|
|
await conn.execute("UPDATE chats SET version=version+1 WHERE id=$1", chat_id)
|
|
if idempotency_key is not None:
|
|
await conn.execute(
|
|
"""INSERT INTO idempotency_keys
|
|
(user_id, method, path, key, request_hash, response_status, response_body)
|
|
VALUES ($1,$2,$3,$4,$5,$6,$7)""",
|
|
sender_id, method, path, idempotency_key,
|
|
request_hash, 201, json.dumps(message, ensure_ascii=False),
|
|
)
|
|
except asyncpg.UniqueViolationError:
|
|
cached = await self._get_idempotent(sender_id, method, path, idempotency_key, request_hash)
|
|
if cached is not None:
|
|
return cached
|
|
raise
|
|
|
|
await self._cache.invalidate_chat(chat_id)
|
|
return message
|
|
|
|
async def _get_idempotent(self, user_id, method, path, key, request_hash):
|
|
if key is None:
|
|
return None
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
"""SELECT request_hash, response_body FROM idempotency_keys
|
|
WHERE user_id=$1 AND method=$2 AND path=$3 AND key=$4""",
|
|
user_id, method, path, key,
|
|
)
|
|
if row is None:
|
|
return None
|
|
if row["request_hash"] != request_hash:
|
|
raise ValueError("idempotency_conflict")
|
|
return json.loads(row["response_body"])
|
|
|
|
async def list_messages(self, chat_id, actor_id, limit, cursor):
|
|
async with self._pool.acquire() as conn:
|
|
is_member = await conn.fetchval(
|
|
"SELECT 1 FROM chat_members WHERE chat_id=$1 AND user_id=$2",
|
|
chat_id, actor_id,
|
|
)
|
|
if is_member is None:
|
|
raise ValueError("not_found")
|
|
|
|
cache_key = f"messages:{chat_id}:{limit}:{cursor or ''}"
|
|
cached = await self._cache.get_json(cache_key)
|
|
if cached is not None:
|
|
version_now = await self._get_chat_version(chat_id)
|
|
if cached.get("version") == version_now:
|
|
return cached["items"], cached["next_cursor"]
|
|
await self._cache.delete(cache_key)
|
|
|
|
lock_key = f"lock:{cache_key}"
|
|
got_lock = await self._cache.try_lock(lock_key, ttl=10)
|
|
if not got_lock:
|
|
for _ in range(50):
|
|
await asyncio.sleep(0.1)
|
|
cached = await self._cache.get_json(cache_key)
|
|
if cached is not None:
|
|
version_now = await self._get_chat_version(chat_id)
|
|
if cached.get("version") == version_now:
|
|
return cached["items"], cached["next_cursor"]
|
|
|
|
try:
|
|
version_before = await self._get_chat_version(chat_id)
|
|
async with self._pool.acquire() as conn:
|
|
params = [chat_id]
|
|
where = ""
|
|
if cursor:
|
|
try:
|
|
cur_chat, cur_created, cur_id = cursor.split("|", 2)
|
|
except ValueError:
|
|
raise ValueError("bad_cursor")
|
|
if cur_chat != chat_id:
|
|
raise ValueError("bad_cursor")
|
|
where = "AND (created_at, id) > ($2,$3)"
|
|
params += [datetime.fromisoformat(cur_created.replace("Z", "+00:00")), cur_id]
|
|
rows = await conn.fetch(
|
|
f"""SELECT id, chat_id, sender_id, text, created_at
|
|
FROM messages WHERE chat_id=$1 {where}
|
|
ORDER BY created_at, id LIMIT ${len(params)+1}""",
|
|
*params, limit + 1,
|
|
)
|
|
items = []
|
|
for row in rows[:limit]:
|
|
att_ids = await self._list_attachment_ids_conn(conn, row["id"])
|
|
items.append(self._message_row(row, att_ids))
|
|
next_cursor = None
|
|
if len(rows) > limit:
|
|
last = rows[limit - 1]
|
|
next_cursor = f"{chat_id}|{_now_iso(last['created_at'])}|{last['id']}"
|
|
version_after = await self._get_chat_version(chat_id)
|
|
if got_lock and version_before == version_after:
|
|
await self._cache.set_json(cache_key, {
|
|
"items": items, "next_cursor": next_cursor, "version": version_after,
|
|
})
|
|
return items, next_cursor
|
|
finally:
|
|
if got_lock:
|
|
await self._cache.unlock(lock_key)
|
|
|
|
async def _get_chat_version(self, chat_id: str) -> int:
|
|
async with self._pool.acquire() as conn:
|
|
row = await conn.fetchval("SELECT version FROM chats WHERE id=$1", chat_id)
|
|
return int(row) if row is not None else 0
|
|
|
|
@staticmethod
|
|
def _user_row(row) -> dict:
|
|
return {"id": str(row["id"]), "username": row["username"],
|
|
"display_name": row["display_name"], "created_at": _now_iso(row["created_at"])}
|
|
|
|
@staticmethod
|
|
def _message_row(row, attachment_ids=None) -> dict:
|
|
return {"id": str(row["id"]), "chat_id": str(row["chat_id"]),
|
|
"sender_id": str(row["sender_id"]), "text": row["text"],
|
|
"created_at": _now_iso(row["created_at"]),
|
|
"attachment_ids": attachment_ids or []}
|
|
|
|
@staticmethod
|
|
def _attachment_row(row, public: bool) -> dict:
|
|
variants_raw = row["variants"]
|
|
if isinstance(variants_raw, str):
|
|
try:
|
|
variants = json.loads(variants_raw)
|
|
except json.JSONDecodeError:
|
|
variants = []
|
|
else:
|
|
variants = variants_raw or []
|
|
|
|
result = {
|
|
"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"], "status": row["status"],
|
|
"variants": variants,
|
|
"error_code": row["error_code"],
|
|
"created_at": _now_iso(row["created_at"]),
|
|
"updated_at": _now_iso(row["updated_at"]),
|
|
}
|
|
if not public:
|
|
result["_object_key"] = row["object_key"]
|
|
variant_keys_raw = row["variant_keys"]
|
|
if isinstance(variant_keys_raw, str):
|
|
try:
|
|
result["_variant_keys"] = json.loads(variant_keys_raw)
|
|
except json.JSONDecodeError:
|
|
result["_variant_keys"] = {}
|
|
else:
|
|
result["_variant_keys"] = variant_keys_raw or {}
|
|
return result |