Skip to content
1        10        20        30        40        50        60        70        80        90        1001CREATE EXTENSION IF NOT EXISTS pgcrypto;23CREATE TABLE users (4    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),5    email TEXT NOT NULL UNIQUE,6    password_hash TEXT NOT NULL,7    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),8    CONSTRAINT users_email_lowercase CHECK (email = lower(email))9);1011CREATE TABLE expenses (12    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),13    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,14    amount_cents BIGINT NOT NULL CHECK (amount_cents BETWEEN 1 AND 100000000000),15    currency CHAR(3) NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),16    description VARCHAR(500) NOT NULL CHECK (length(trim(description)) BETWEEN 1 AND 500),17    occurred_on DATE NOT NULL,18    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),19    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()20);2122CREATE INDEX expenses_user_occurred_on_idx ON expenses (user_id, occurred_on DESC, created_at DESC);