8da149b84e
- Add asyncpg connection pool (db/pool.py) with JSONB codec registration - Add schema.sql with users, clients, dropdown_categories, export_templates, sheets tables - Add migrate_json.py one-time migration script for existing JSON data - Rewrite user_store, sheets/manager, api/clients, api/dropdowns, api/export as async DB-backed - Update all callers (auth, sheets, admin, ai_command, export) to await async functions - Add postgres:16-alpine service to docker-compose with named volume and health check - App container depends_on postgres; DATABASE_URL injected via env - Schema applied automatically on startup; global categories seeded if DB is empty Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
86 lines
2.9 KiB
Python
86 lines
2.9 KiB
Python
"""
|
|
User store — PostgreSQL-backed.
|
|
Keyed by Azure AD oid (object ID).
|
|
"""
|
|
|
|
import logging
|
|
from datetime import datetime, timezone
|
|
from typing import Dict, Optional
|
|
|
|
from ..config_runtime import server_config
|
|
from ..db.pool import get_pool
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
def _row_to_dict(row) -> Dict:
|
|
return {
|
|
'id': row['id'],
|
|
'email': row['email'],
|
|
'name': row['name'],
|
|
'role': row['role'],
|
|
'active': row['active'],
|
|
'created': row['created_at'].isoformat() if row['created_at'] else None,
|
|
'last_seen': row['last_seen_at'].isoformat() if row['last_seen_at'] else None,
|
|
}
|
|
|
|
|
|
async def get_user(user_id: str) -> Optional[Dict]:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow('SELECT * FROM users WHERE id = $1', user_id)
|
|
return _row_to_dict(row) if row else None
|
|
|
|
|
|
async def upsert_user(user_id: str, email: str, name: str, role: Optional[str] = None) -> Dict:
|
|
"""
|
|
Create or update user. On first creation, grants admin if email is in ADMIN_EMAILS.
|
|
"""
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
existing = await conn.fetchrow('SELECT * FROM users WHERE id = $1', user_id)
|
|
|
|
if existing is None:
|
|
default_role = 'admin' if email and email.lower() in server_config.ADMIN_EMAILS else 'user'
|
|
row = await conn.fetchrow('''
|
|
INSERT INTO users (id, email, name, role, active)
|
|
VALUES ($1, $2, $3, $4, TRUE)
|
|
RETURNING *
|
|
''', user_id, email or '', name or '', role or default_role)
|
|
else:
|
|
new_role = role if role is not None else existing['role']
|
|
row = await conn.fetchrow('''
|
|
UPDATE users
|
|
SET email = $2, name = $3, role = $4, last_seen_at = NOW()
|
|
WHERE id = $1
|
|
RETURNING *
|
|
''', user_id, email or existing['email'], name or existing['name'], new_role)
|
|
|
|
return _row_to_dict(row)
|
|
|
|
|
|
async def list_users() -> list:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
rows = await conn.fetch('SELECT * FROM users ORDER BY last_seen_at DESC')
|
|
return [_row_to_dict(r) for r in rows]
|
|
|
|
|
|
async def set_role(user_id: str, role: str) -> Optional[Dict]:
|
|
if role not in ('user', 'admin'):
|
|
return None
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
'UPDATE users SET role = $2 WHERE id = $1 RETURNING *', user_id, role
|
|
)
|
|
return _row_to_dict(row) if row else None
|
|
|
|
|
|
async def set_active(user_id: str, active: bool) -> Optional[Dict]:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow(
|
|
'UPDATE users SET active = $2 WHERE id = $1 RETURNING *', user_id, active
|
|
)
|
|
return _row_to_dict(row) if row else None
|