schema-sqlite.sql

-- Cost Sharing API Database Schema
-- SQLite3 Schema for Flask Application
-- 
-- This schema implements the data model for the Cost Sharing API.
-- 
-- All monetary amounts are stored as NUMERIC(10,2) for precision.
-- Expense dates are stored as DATE (YYYY-MM-DD format).
-- IDs are sequential integers, auto-generated by the database.

-- Enable foreign key constraints (required for SQLite)
PRAGMA foreign_keys = ON;

-- ============================================================================
-- USERS TABLE
-- ============================================================================
-- Stores user accounts. Users can be created when added to a group.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL
);

-- ============================================================================
-- GROUPS TABLE
-- ============================================================================
-- Stores cost-sharing groups. Each group has a creator and multiple members.

CREATE TABLE groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    created_by_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    CHECK (length(name) >= 1),
    CHECK (description IS NULL OR length(description) <= 500)
);

-- ============================================================================
-- GROUP MEMBERS TABLE (Junction Table)
-- ============================================================================
-- Many-to-many relationship between users and groups.

CREATE TABLE group_members (
    group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (group_id, user_id)
);

-- ============================================================================
-- EXPENSES TABLE
-- ============================================================================
-- Stores expenses within groups. Each expense is paid by one user and
-- split among multiple users (tracked in expense_participants table).

CREATE TABLE expenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    description TEXT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    expense_date DATE NOT NULL,
    paid_by_user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    CHECK (amount >= 0.01),
    CHECK (length(description) >= 1 AND length(description) <= 200)
);

-- ============================================================================
-- EXPENSE PARTICIPANTS TABLE (Junction Table)
-- ============================================================================
-- Tracks which users an expense is split between.
-- The paid_by user MUST be in this table (enforced by application logic).

CREATE TABLE expense_participants (
    expense_id INTEGER NOT NULL REFERENCES expenses(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    PRIMARY KEY (expense_id, user_id)
);