database

Database initialization and admin bootstrap for Supabase PostgreSQL.

Handles: - Schema creation (4 tables: users, refresh_tokens, sessions, messages) - Admin user bootstrap - Legacy session migration from markdown files to PostgreSQL - Async query helpers for FastAPI endpoints

All queries use supabase-py → PostgREST, which provides parameterized query binding and is immune to SQL injection through the normal API. The only raw SQL vector is _db_execute_sql(), which is private and accepts only static trusted input (supabase_schema.sql contents). Table names are validated against an allowlist in all query helpers.

db_delete(table, **filters) async

DELETE rows matching filters. PostgREST parameterizes — safe from injection.

db_fetch_all(table, columns='*', order=None, ascending=True, limit=100, **filters) async

SELECT from a table and return all matching rows.

PostgREST parameterizes all filters — immune to SQL injection. List-valued filters become IN clauses; order may be a single column name or a list of (column, ascending) tuples for deterministic multi-column ordering.

db_fetch_one(table, columns='*', **filters) async

SELECT from a table and return the first matching row, or None.

PostgREST parameterizes all filters — immune to SQL injection.

db_insert(table, data) async

INSERT a row. PostgREST parameterizes values — safe from injection.

db_update(table, data, **filters) async

UPDATE rows matching filters. PostgREST parameterizes — safe from injection.

ensure_admin() async

Create the admin user if it doesn't already exist.

get_supabase()

Get or create the singleton Supabase client (sync).

Uses the service_role key for full database access. Thread-safe for use with asyncio.to_thread() in async endpoints.

init_db() async

Initialize the database schema idempotently.

Tries to create tables via exec_sql if they don't exist yet. Safe to call multiple times.

migrate_legacy_sessions() async

Migrate legacy markdown sessions to Supabase. Returns count migrated.