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>
101 lines
3.2 KiB
Python
101 lines
3.2 KiB
Python
"""
|
|
Client management API — PostgreSQL-backed.
|
|
"""
|
|
|
|
import logging
|
|
import time
|
|
import random
|
|
from datetime import datetime, timezone
|
|
|
|
from quart import Blueprint, jsonify, request
|
|
|
|
from ..auth.middleware import auth_required, admin_required
|
|
from ..db.pool import get_pool
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
clients_bp = Blueprint('clients', __name__, url_prefix='/api/clients')
|
|
|
|
|
|
async def load_clients() -> list:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
rows = await conn.fetch('SELECT * FROM clients ORDER BY name')
|
|
return [_row_to_dict(r) for r in rows]
|
|
|
|
|
|
async def get_client_by_id(client_id: str) -> dict | None:
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow('SELECT * FROM clients WHERE id = $1', client_id)
|
|
return _row_to_dict(row) if row else None
|
|
|
|
|
|
async def set_client_custom_dropdowns(client_id: str, value: bool):
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
await conn.execute(
|
|
'UPDATE clients SET has_custom_dropdowns = $2 WHERE id = $1',
|
|
client_id, value
|
|
)
|
|
|
|
|
|
def _row_to_dict(row) -> dict:
|
|
return {
|
|
'id': row['id'],
|
|
'name': row['name'],
|
|
'hasCustomDropdowns': row['has_custom_dropdowns'],
|
|
'created': row['created_at'].isoformat() if row['created_at'] else None,
|
|
}
|
|
|
|
|
|
@clients_bp.route('', methods=['GET'])
|
|
@auth_required
|
|
async def list_clients():
|
|
return jsonify({'clients': await load_clients()})
|
|
|
|
|
|
@clients_bp.route('', methods=['POST'])
|
|
@admin_required
|
|
async def create_client():
|
|
body = await request.get_json() or {}
|
|
name = body.get('name', '').strip()
|
|
if not name:
|
|
return jsonify({'error': 'name_required', 'message': 'Client name is required'}), 400
|
|
|
|
client_id = f"client_{int(time.time())}{random.randint(100, 999)}"
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow('''
|
|
INSERT INTO clients (id, name, has_custom_dropdowns)
|
|
VALUES ($1, $2, FALSE)
|
|
RETURNING *
|
|
''', client_id, name)
|
|
return jsonify({'client': _row_to_dict(row)}), 201
|
|
|
|
|
|
@clients_bp.route('/<client_id>', methods=['DELETE'])
|
|
@admin_required
|
|
async def delete_client(client_id: str):
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
# Cascades to dropdown_categories via FK; export templates by scope
|
|
await conn.execute('DELETE FROM clients WHERE id = $1', client_id)
|
|
await conn.execute("DELETE FROM export_templates WHERE scope = $1", f'client:{client_id}')
|
|
return jsonify({'success': True})
|
|
|
|
|
|
@clients_bp.route('/<client_id>', methods=['PATCH'])
|
|
@admin_required
|
|
async def update_client(client_id: str):
|
|
body = await request.get_json() or {}
|
|
pool = get_pool()
|
|
async with pool.acquire() as conn:
|
|
row = await conn.fetchrow('SELECT * FROM clients WHERE id = $1', client_id)
|
|
if not row:
|
|
return jsonify({'error': 'not_found'}), 404
|
|
name = body.get('name', row['name']).strip() or row['name']
|
|
row = await conn.fetchrow(
|
|
'UPDATE clients SET name = $2 WHERE id = $1 RETURNING *', client_id, name
|
|
)
|
|
return jsonify({'client': _row_to_dict(row)})
|