# Database Schema — Desi Clans

**Version:** 1.2  
**Engine:** PostgreSQL 15+  
**ORM:** SQLAlchemy 2.x  
**Migrations:** Alembic

---

## 1. Overview

The database supports:

1. **Catalog (Phase 6)** — `venues`, `events`, `event_media`, `ticket_types`  
2. **Legacy content** — `event_editions`, `media_assets` (admin-era tables)  
3. **Marketing** — platform links, newsletter subscribers, outbound clicks  
4. **Admin** — admin users and roles  
5. **Accounts** — attendee `users` (Phase 8)  
6. **Ticketing (Phase 9)** — orders, order items, issued tickets  

---

## 2. Catalog ERD (implemented)

```
users ──< orders ──< order_items ──< tickets
              │            │
              │            └──> ticket_types
              └──> events ──< event_media
                        │
                        └──< ticket_types
```

---

## 3. Tables — catalog

### `venues`

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| slug | VARCHAR(120) | UNIQUE, NOT NULL |
| name | VARCHAR(200) | NOT NULL |
| area | VARCHAR(120) | NOT NULL |
| city | VARCHAR(80) | NOT NULL, default Bengaluru |
| address | VARCHAR(512) | NOT NULL |
| map_url | VARCHAR(512) | NULL |
| description | TEXT | NULL |
| created_at | TIMESTAMPTZ | DEFAULT now() |
| updated_at | TIMESTAMPTZ | DEFAULT now() |

---

### `events`

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| slug | VARCHAR(200) | UNIQUE, NOT NULL |
| title | VARCHAR(240) | NOT NULL |
| category | eventtype ENUM | NOT NULL, INDEX |
| status | eventstatus ENUM | NOT NULL, default draft, INDEX |
| venue_id | INT | FK → venues.id RESTRICT, INDEX |
| description | TEXT | NULL |
| starts_at | TIMESTAMPTZ | NOT NULL, INDEX |
| ends_at | TIMESTAMPTZ | NULL |
| is_featured | BOOLEAN | default false |
| cover_image_url | VARCHAR(1024) | NULL |
| cover_image_alt | VARCHAR(300) | NULL |
| meta_description | VARCHAR(320) | NULL |
| created_at | TIMESTAMPTZ | DEFAULT now() |
| updated_at | TIMESTAMPTZ | DEFAULT now() |

**Indexes:** `(status, starts_at)`, `(category, status)`

**`eventstatus`:** `draft`, `published`, `cancelled`

---

### `event_media`

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| event_id | INT | FK → events.id CASCADE, INDEX |
| image_url | VARCHAR(1024) | NOT NULL |
| alt_text | VARCHAR(300) | NULL |
| sort_order | INT | default 0 |
| created_at | TIMESTAMPTZ | DEFAULT now() |

---

### `ticket_types`

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| event_id | INT | FK → events.id CASCADE, INDEX |
| name | VARCHAR(120) | NOT NULL |
| description | VARCHAR(512) | NULL |
| price_paise | INT | NOT NULL |
| quantity_total | INT | NOT NULL |
| quantity_sold | INT | default 0 |
| status | tickettypestatus ENUM | default active |
| sort_order | INT | default 0 |
| created_at | TIMESTAMPTZ | DEFAULT now() |
| updated_at | TIMESTAMPTZ | DEFAULT now() |

**`tickettypestatus`:** `active`, `sold_out`, `inactive`

**Check:** `quantity_sold <= quantity_total` (migration `20260404_0007`)

Inventory updates use row-level locks (`SELECT … FOR UPDATE`) during checkout.

---

### `users` (attendees)

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| email | VARCHAR(255) | UNIQUE, NOT NULL |
| hashed_password | VARCHAR(255) | NOT NULL (bcrypt) |
| full_name | VARCHAR(120) | NULL |
| is_active | BOOLEAN | default true |
| created_at | TIMESTAMPTZ | DEFAULT now() |

---

### `orders`

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| user_id | INT | FK → users.id RESTRICT, INDEX |
| event_id | INT | FK → events.id RESTRICT, INDEX |
| status | orderstatus ENUM | NOT NULL, INDEX |
| total_paise | INT | NOT NULL (server-calculated) |
| currency | VARCHAR(3) | default INR |
| payment_reference | VARCHAR(64) | NULL (mock `mock_*` until gateway) |
| paid_at | TIMESTAMPTZ | NULL |
| created_at | TIMESTAMPTZ | DEFAULT now() |
| updated_at | TIMESTAMPTZ | DEFAULT now() |

**`orderstatus`:** `pending`, `paid`, `cancelled`, `failed`  
Checkout currently completes in one transaction with mock payment → `paid`.

---

### `order_items`

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| order_id | INT | FK → orders.id CASCADE, INDEX |
| ticket_type_id | INT | FK → ticket_types.id RESTRICT, INDEX |
| quantity | INT | NOT NULL, > 0 |
| unit_price_paise | INT | NOT NULL (snapshot at purchase) |
| line_total_paise | INT | NOT NULL |

---

### `tickets` (issued passes)

| Column | Type | Constraints |
|--------|------|-------------|
| id | SERIAL | PK |
| code | VARCHAR(32) | UNIQUE, NOT NULL (e.g. `DC-…`) |
| order_id | INT | FK → orders.id CASCADE, INDEX |
| order_item_id | INT | FK → order_items.id CASCADE, INDEX |
| ticket_type_id | INT | FK → ticket_types.id RESTRICT, INDEX |
| event_id | INT | FK → events.id RESTRICT, INDEX |
| user_id | INT | FK → users.id RESTRICT, INDEX |
| status | ticketstatus ENUM | NOT NULL, INDEX |
| created_at | TIMESTAMPTZ | DEFAULT now() |

**`ticketstatus`:** `valid`, `cancelled`, `redeemed`  
**Index:** `(user_id, status)`

---

## 4. Seed data

```bash
python scripts/seed_catalog.py
```

Creates Bengaluru venues (Palace Grounds, KTPO Whitefield, HSR Layout) and published events for **Holi**, **Garba**, and **Dandiya** with media URLs and ticket tiers.

---

## 5. Legacy tables

See prior revisions for `event_editions`, `media_assets`, `admin_users`, `platform_links`, `newsletter_subscribers`, `outbound_clicks`, `alert_dispatches`.

---

## 6. Migration policy

- Revision `20260404_0005` adds catalog tables.  
- Revision `20260404_0006` adds attendee `users`.  
- Revision `20260404_0007` adds `orders`, `order_items`, `tickets`.  
- If the DB was created via `create_all` before Alembic history existed, run `alembic stamp 20260404_0004` once, then `alembic upgrade head`.

---

## 7. Related

- [API_SPEC.md](./API_SPEC.md)