File size: 3,641 Bytes
9f1c9d5 5d5a5b6 9f1c9d5 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | -- ============================================================================
-- Personal Assistant — authentication schema
-- ============================================================================
-- Run against your Postgres database, e.g.:
-- psql "$DATABASE_URL" -f db/schema.sql
--
-- The matching server code lives in:
-- src/web/auth.py (langgraph_sdk.Auth JWT verification)
-- src/web/login.py (/auth/signup, /auth/login custom routes)
-- ============================================================================
BEGIN;
-- ----------------------------------------------------------------------------
-- users
-- ----------------------------------------------------------------------------
-- One row per account. Passwords are stored as bcrypt hashes only — the
-- plaintext password never leaves the /auth/signup handler.
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
username TEXT NOT NULL,
email TEXT,
password_hash TEXT NOT NULL,
display_name TEXT,
role TEXT NOT NULL DEFAULT 'user',
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Case-insensitive uniqueness for usernames and emails.
CREATE UNIQUE INDEX IF NOT EXISTS users_username_lower_idx
ON users (lower(username));
CREATE UNIQUE INDEX IF NOT EXISTS users_email_lower_idx
ON users (lower(email))
WHERE email IS NOT NULL;
-- ----------------------------------------------------------------------------
-- updated_at trigger
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS users_set_updated_at ON users;
CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
-- ----------------------------------------------------------------------------
-- token_usage
-- ----------------------------------------------------------------------------
-- One row per user per calendar month. ``period_start`` is always the first
-- day of the month (UTC); a new month simply gets a fresh row, which is how
-- the allowance "refreshes" on the first day of every month.
CREATE TABLE IF NOT EXISTS token_usage (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
period_start DATE NOT NULL,
tokens_used BIGINT NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, period_start)
);
DROP TRIGGER IF EXISTS token_usage_set_updated_at ON token_usage;
CREATE TRIGGER token_usage_set_updated_at
BEFORE UPDATE ON token_usage
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
COMMIT;
-- ============================================================================
-- Notes
-- ============================================================================
-- * Requires the pgcrypto extension for gen_random_uuid(). On Postgres 13+
-- it is built in; on older versions run:
-- CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- * The application connects with the DATABASE_URL environment variable,
-- e.g.: postgresql://user:password@localhost:5432/personal_assistant
-- * JWT signing uses the JWT_SECRET environment variable (HS256).
-- ============================================================================
|