> ## Documentation Index
> Fetch the complete documentation index at: https://docs.zeus.ttr.gg/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> PostgreSQL schema for Zeus

## Overview

Zeus uses PostgreSQL with a normalized schema optimized for transaction tracking.

## Entity Relationship Diagram

```mermaid theme={null}
erDiagram
    users {
        uuid id PK
        varchar email
        varchar name
        varchar password_hash
        timestamptz created_at
        timestamptz updated_at
    }
    
    wallets {
        uuid id PK
        uuid user_id FK
        varchar name
        varchar currency
        decimal balance
        timestamptz created_at
        timestamptz updated_at
    }
    
    categories {
        uuid id PK
        uuid user_id FK
        varchar name
        varchar type
        integer color
        varchar icon
        timestamptz created_at
        timestamptz updated_at
    }
    
    transactions {
        uuid id PK
        uuid user_id FK
        uuid wallet_id FK
        uuid category_id FK
        decimal amount
        varchar type
        text description
        date date
        timestamptz created_at
        timestamptz updated_at
    }
    
    users ||--o{ wallets : owns
    users ||--o{ categories : owns
    users ||--o{ transactions : records
    wallets ||--o{ transactions : contains
    categories ||--o{ transactions : categorizes
```

## Users Table

```sql theme={null}
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
```

## Wallets Table

```sql theme={null}
CREATE TABLE wallets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_wallets_user_id ON wallets(user_id);
CREATE INDEX idx_wallets_created_at ON wallets(created_at DESC);
```

## Categories Table

```sql theme={null}
CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(10) NOT NULL CHECK (type IN ('income', 'expense')),
    color INTEGER,
    icon VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_categories_user_id ON categories(user_id);
CREATE INDEX idx_categories_type ON categories(type);
```

## Transactions Table

```sql theme={null}
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    wallet_id UUID NOT NULL REFERENCES wallets(id) ON DELETE CASCADE,
    category_id UUID REFERENCES categories(id) ON DELETE SET NULL,
    amount DECIMAL(15, 2) NOT NULL CHECK (amount > 0),
    type VARCHAR(10) NOT NULL CHECK (type IN ('income', 'expense', 'transfer')),
    description TEXT,
    date DATE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_transactions_user_id ON transactions(user_id);
CREATE INDEX idx_transactions_wallet_id ON transactions(wallet_id);
CREATE INDEX idx_transactions_date ON transactions(date DESC);
CREATE INDEX idx_transactions_category_id ON transactions(category_id);
```

## Data Types

| Type             | Usage                         |
| ---------------- | ----------------------------- |
| `UUID`           | Primary keys and foreign keys |
| `VARCHAR(n)`     | Strings with length limits    |
| `TEXT`           | Unrestricted strings          |
| `DECIMAL(15, 2)` | Monetary amounts              |
| `TIMESTAMPTZ`    | Timestamps with timezone      |
| `DATE`           | Date only (no time)           |
| `INTEGER`        | Color values, counts          |