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>
48 lines
1.7 KiB
SQL
48 lines
1.7 KiB
SQL
-- AC Tool PostgreSQL schema
|
|
-- Run this once (idempotent — safe to re-run).
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
email TEXT NOT NULL DEFAULT '',
|
|
name TEXT NOT NULL DEFAULT '',
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS clients (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
has_custom_dropdowns BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- NULL client_id = global hierarchy; non-null = per-client override
|
|
CREATE TABLE IF NOT EXISTS dropdown_categories (
|
|
id SERIAL PRIMARY KEY,
|
|
client_id TEXT REFERENCES clients(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'Active',
|
|
media_types JSONB NOT NULL DEFAULT '[]'
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_dropdown_cat_client ON dropdown_categories(client_id);
|
|
|
|
-- scope: 'global' | 'client:<id>' | 'user:<id>'
|
|
CREATE TABLE IF NOT EXISTS export_templates (
|
|
scope TEXT PRIMARY KEY,
|
|
columns JSONB NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sheets (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
client_id TEXT REFERENCES clients(id) ON DELETE SET NULL,
|
|
data JSONB NOT NULL DEFAULT '[]',
|
|
item_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_sheets_user ON sheets(user_id);
|