# Database Design Deep Dive

This document explains the database schema, design decisions, relationships, and migration strategies used in the Bug Bounty Platform.

---

## Table of Contents

1. [Schema Overview](#schema-overview)
2. [Entity Relationship Diagram](#entity-relationship-diagram)
3. [Table Schemas](#table-schemas)
4. [Design Decisions](#design-decisions)
5. [Indexes and Performance](#indexes-and-performance)
6. [Migrations](#migrations)
7. [Common Queries](#common-queries)

---

## Schema Overview

The platform uses **PostgreSQL 18** with 9 main tables:

| Table | Description | Rows (typical) |
|-------|-------------|----------------|
| `users` | User accounts (researchers + companies) | 1K-100K |
| `refresh_tokens` | JWT refresh tokens | 5K-500K |
| `programs` | Bug bounty programs | 100-10K |
| `assets` | Program scope (domains, APIs) | 500-50K |
| `reward_tiers` | Bounty amounts by severity | 500-50K |
| `reports` | Vulnerability submissions | 10K-1M |
| `comments` | Triage communication | 50K-5M |
| `attachments` | Proof-of-concept files | 10K-1M |

---

## Entity Relationship Diagram

```
┌──────────────────────────────┐
│          users               │
│──────────────────────────────│
│ id (UUID v7) PK              │
│ email (unique, indexed)      │
│ hashed_password              │
│ full_name                    │
│ role (enum)                  │
│ is_active                    │
│ is_verified                  │
│ token_version                │
│ company_name                 │
│ bio                          │
│ website                      │
│ reputation_score             │
│ created_at, updated_at       │
└──────────────┬───────────────┘
               │
    ┌──────────┼──────────┬──────────────┐
    │          │          │              │
    │ 1        │ 1        │ 1            │
    │          │          │              │
    ▼ *        ▼ *        ▼ *            ▼ *
┌─────────────┐ ┌──────────┐ ┌────────────┐ ┌────────────────┐
│refresh_     │ │programs  │ │  reports   │ │                │
│tokens       │ │          │ │            │ │                │
│─────────────│ │──────────│ │────────────│ │                │
│id PK        │ │id PK     │ │id PK       │ │                │
│user_id FK   │ │company_id│ │program_id  │ │                │
│token_hash   │ │name      │ │researcher  │ │                │
│device_info  │ │slug (idx)│ │  _id FK    │ │                │
│family_id    │ │description│ │title      │ │                │
│ip_address   │ │rules     │ │description │ │                │
│expires_at   │ │response  │ │steps_to    │ │                │
│created_at   │ │  _sla_hrs│ │  _reproduce│ │                │
└─────────────┘ │status    │ │impact      │ │                │
                │  (indexed)│ │severity    │ │                │
                │visibility│ │  _submitted│ │                │
                │created_at│ │severity    │ │                │
                │updated_at│ │  _final    │ │                │
                └────┬─────┘ │status (idx)│ │                │
                     │       │cvss_score  │ │                │
           ┌─────────┼────┐  │cwe_id      │ │                │
           │         │    │  │bounty_amt  │ │                │
           │ 1       │ 1  │  │duplicate   │ │                │
           │         │    │  │  _of_id    │ │                │
           ▼ *       ▼ *  │  │triaged_at  │ │                │
        ┌─────┐  ┌─────┐ │  │resolved_at │ │                │
        │asset│  │reward│ │  │disclosed_at│ │                │
        │     │  │tier │ │  │created_at  │ │                │
        │─────│  │─────│ │  │updated_at  │ │                │
        │id PK│  │id PK│ │  └──────┬─────┘ │                │
        │prog │  │prog │ │         │       │                │
        │ _id │  │ _id │ │    ┌────┼────┐  │                │
        │type │  │seve │ │    │ 1       │ 1│                │
        │targ │  │ rity│ │    │         │  │                │
        │ et  │  │amnt │ │    ▼ *       ▼ *│                │
        │crea │  │crea │ │  ┌────────┐ ┌──┴────┐            │
        │ted  │  │ted  │ │  │comments│ │attach │            │
        │     │  │     │ │  │        │ │ments  │            │
        └─────┘  └─────┘ │  │────────│ │───────│            │
                         │  │id PK   │ │id PK  │            │
                         │  │report  │ │report │            │
                         │  │  _id FK│ │  _id  │            │
                         │  │author  │ │  FK   │            │
                         │  │  _id FK│ │file   │            │
                         │  │content │ │  _name│            │
                         │  │created │ │file   │            │
                         │  │  _at   │ │  _size│            │
                         │  │updated │ │file   │            │
                         │  │  _at   │ │  _type│            │
                         │  └────────┘ │s3_key │            │
                         │             │created│            │
                         │             │  _at  │            │
                         │             └───────┘            │
                         │                                  │
                         └──────────────────────────────────┘
```

---

## Table Schemas

### users

**Purpose:** Store user accounts (researchers, companies, admins)

```sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    email VARCHAR(255) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    role VARCHAR(50) DEFAULT 'user',  -- SafeEnum: stores value, not name
    token_version INTEGER DEFAULT 0,
    company_name VARCHAR(255),
    bio TEXT,
    website VARCHAR(255),
    reputation_score INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

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

**Key Fields:**

- `id` - UUID v7 (time-sortable, globally unique)
- `email` - Unique identifier (indexed for fast lookups)
- `hashed_password` - Argon2id hash (never store plaintext)
- `role` - SafeEnum pattern (stores "user", not "USER")
- `token_version` - Increment to invalidate all tokens
- `reputation_score` - Researcher reputation (bounties earned, reports accepted)

**Design Decisions:**

1. **Single user table** - Both researchers and companies use the same table
   - Alternative: Separate `researchers` and `companies` tables
   - Reason: Same authentication, reduces JOIN complexity
   - Trade-off: Some fields unused (researchers don't have `company_name`)

2. **Soft deletes NOT used** - When a user is deleted, they're gone
   - Alternative: Add `deleted_at` column
   - Reason: GDPR compliance requires true deletion
   - Exception: Admin can set `is_active = false` to suspend accounts

3. **Token versioning** - Increment to invalidate all tokens instantly
   - Alternative: Maintain token blacklist
   - Reason: Simpler, no need to clean up old blacklist entries

### programs

**Purpose:** Bug bounty programs hosted by companies

```sql
CREATE TABLE programs (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    company_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    rules TEXT,
    response_sla_hours INTEGER DEFAULT 72,
    status VARCHAR(50) DEFAULT 'draft',  -- draft, active, paused, closed
    visibility VARCHAR(50) DEFAULT 'public',  -- public, private, invite_only
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_programs_slug ON programs(slug);
CREATE INDEX idx_programs_status ON programs(status);
CREATE INDEX idx_programs_company_id ON programs(company_id);
```

**Key Fields:**

- `slug` - URL-friendly identifier (`/programs/acme-corp`)
- `status` - Program lifecycle (indexed for filtering active programs)
- `visibility` - Who can see the program
- `response_sla_hours` - How quickly company must respond (72h default)

**Design Decisions:**

1. **Slug for URLs** - Use slug instead of ID in URLs
   - Alternative: Use UUID in URL (`/programs/018d3f54-8c3a-7000`)
   - Reason: Better UX, SEO-friendly
   - Constraint: Must be unique and immutable

2. **Status enum** - Draft → Active → Paused/Closed
   - Draft: Not visible, company is setting up
   - Active: Accepting submissions
   - Paused: Temporarily closed (no new submissions)
   - Closed: Permanently closed

3. **SLA tracking** - `response_sla_hours` sets expectation
   - Used to calculate if company is meeting SLA
   - Can be used for automated notifications

### reports

**Purpose:** Vulnerability reports submitted by researchers

```sql
CREATE TABLE reports (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    program_id UUID NOT NULL REFERENCES programs(id) ON DELETE CASCADE,
    researcher_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    steps_to_reproduce TEXT,
    impact TEXT,
    severity_submitted VARCHAR(50) DEFAULT 'medium',
    severity_final VARCHAR(50),
    status VARCHAR(50) DEFAULT 'new',  -- new, triaging, accepted, etc.
    cvss_score NUMERIC(3, 1),  -- 0.0 to 10.0
    cwe_id VARCHAR(20),  -- CWE-79, CWE-89, etc.
    bounty_amount INTEGER,
    duplicate_of_id UUID REFERENCES reports(id) ON DELETE SET NULL,
    triaged_at TIMESTAMPTZ,
    resolved_at TIMESTAMPTZ,
    disclosed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reports_program_id ON reports(program_id);
CREATE INDEX idx_reports_researcher_id ON reports(researcher_id);
CREATE INDEX idx_reports_status ON reports(status);
```

**Key Fields:**

- `severity_submitted` - Researcher's assessment
- `severity_final` - Company's final assessment (may differ)
- `status` - Report lifecycle (indexed for filtering)
- `cvss_score` - Common Vulnerability Scoring System (0.0-10.0)
- `cwe_id` - Common Weakness Enumeration (CWE-79 = XSS)
- `duplicate_of_id` - Self-referencing FK for duplicate tracking
- `triaged_at`, `resolved_at`, `disclosed_at` - Lifecycle timestamps

**Design Decisions:**

1. **Two severity fields** - Submitted vs final
   - Reason: Researcher may overestimate severity
   - Company can adjust during triage
   - Transparency: Both values are visible

2. **Status workflow** - Linear progression with branches
   ```
   NEW → TRIAGING → NEEDS_MORE_INFO → TRIAGING
         ↓
         ACCEPTED → RESOLVED → DISCLOSED
         ↓
         DUPLICATE
         ↓
         NOT_APPLICABLE
         ↓
         INFORMATIVE
   ```

3. **Duplicate tracking** - `duplicate_of_id` links to original
   - Alternative: Store duplicate IDs in array
   - Reason: Simplicity, can traverse duplicate chain
   - Caveat: Must prevent circular references

4. **CVSS + CWE** - Industry-standard vulnerability classification
   - CVSS score: Numeric severity (6.0-6.9 = medium)
   - CWE ID: Weakness category (CWE-79 = XSS)
   - Both are optional but recommended

### refresh_tokens

**Purpose:** Store JWT refresh tokens for authentication

```sql
CREATE TABLE refresh_tokens (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL,  -- SHA-256 hash
    device_info VARCHAR(255),
    ip_address VARCHAR(45),  -- IPv6-compatible
    family_id UUID NOT NULL,  -- For token rotation detection
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);
CREATE INDEX idx_refresh_tokens_token_hash ON refresh_tokens(token_hash);
CREATE INDEX idx_refresh_tokens_expires_at ON refresh_tokens(expires_at);
```

**Key Fields:**

- `token_hash` - SHA-256 hash of refresh token (not plaintext!)
- `family_id` - Tracks token rotation chain
- `device_info` - User agent string
- `ip_address` - For security monitoring
- `expires_at` - Tokens expire after 7 days

**Design Decisions:**

1. **Hash tokens** - Store SHA-256 hash, not plaintext
   - If database is compromised, attacker can't use tokens
   - Must hash token before querying database

2. **Token rotation** - Each refresh creates new token
   - Old token is deleted
   - New token has same `family_id`
   - If old token is reused → detected by `family_id` mismatch

3. **Multi-device support** - User can have multiple refresh tokens
   - Each device/session gets its own token
   - Logout deletes specific token
   - Logout-all deletes all tokens for user

### assets

**Purpose:** Define program scope (domains, APIs, mobile apps)

```sql
CREATE TABLE assets (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    program_id UUID NOT NULL REFERENCES programs(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL,  -- web, api, mobile, other
    target VARCHAR(255) NOT NULL,  -- *.example.com, api.example.com
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_assets_program_id ON assets(program_id);
```

**Key Fields:**

- `type` - Asset category (web app, API, mobile app, etc.)
- `target` - The actual asset (domain, API endpoint, app package name)

**Design Decisions:**

1. **Separate table** - Not embedded in `programs`
   - Reason: Programs can have many assets
   - Easier to add/remove scope items
   - Can query "which programs include example.com?"

### reward_tiers

**Purpose:** Define bounty amounts by severity

```sql
CREATE TABLE reward_tiers (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    program_id UUID NOT NULL REFERENCES programs(id) ON DELETE CASCADE,
    severity VARCHAR(50) NOT NULL,  -- critical, high, medium, low
    amount INTEGER NOT NULL,  -- in USD cents
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reward_tiers_program_id ON reward_tiers(program_id);
```

**Key Fields:**

- `severity` - Critical, high, medium, low, informational
- `amount` - Bounty in USD cents (e.g., 50000 = $500.00)

**Design Decisions:**

1. **Store cents, not dollars** - Avoid floating point issues
   - `amount = 50000` (integer)
   - Display as `$500.00` in UI
   - Prevents rounding errors

2. **Per-program tiers** - Each program sets its own bounties
   - Alternative: Platform-wide default tiers
   - Reason: Flexibility, companies have different budgets

### comments

**Purpose:** Communication between researcher and company

```sql
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,  -- Internal company notes
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_comments_report_id ON comments(report_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);
```

**Key Fields:**

- `is_internal` - Company-only notes (not visible to researcher)
- `content` - Markdown-formatted text

**Design Decisions:**

1. **Separate table** - Not embedded in `reports`
   - Reason: Reports can have many comments
   - Chronological order preserved
   - Can notify on new comments

2. **Internal comments** - `is_internal = true`
   - Company can discuss report privately
   - Researcher never sees these
   - Useful for triage notes

### attachments

**Purpose:** Proof-of-concept files (screenshots, videos, scripts)

```sql
CREATE TABLE attachments (
    id UUID PRIMARY KEY DEFAULT uuid_v7(),
    report_id UUID NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
    file_name VARCHAR(255) NOT NULL,
    file_size INTEGER NOT NULL,  -- in bytes
    file_type VARCHAR(100) NOT NULL,  -- image/png, video/mp4, etc.
    s3_key VARCHAR(255) NOT NULL,  -- S3 object key
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_attachments_report_id ON attachments(report_id);
```

**Key Fields:**

- `file_name` - Original filename
- `file_size` - In bytes
- `file_type` - MIME type
- `s3_key` - S3 storage key (actual file not in database)

**Design Decisions:**

1. **Metadata only** - Files stored in S3, not database
   - Reason: Database is for structured data, not binary blobs
   - Better performance
   - Cheaper storage

2. **No soft deletes** - When report is deleted, attachments are deleted
   - S3 objects are also deleted (via lifecycle policy or webhook)

---

## Design Decisions

### UUID v7 vs Auto-Increment

**Auto-Increment IDs:**
```sql
id SERIAL PRIMARY KEY  -- 1, 2, 3, 4, ...
```

Problems:
- Predictable (enumerate all records by guessing IDs)
- Not globally unique (can't merge databases)
- Require database round-trip to generate

**UUID v4 (Random):**
```sql
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
```

Problems:
- Random = bad for database indexes
- Not time-sortable (`ORDER BY id` is meaningless)

**UUID v7 (Best of both):**
```sql
id UUID PRIMARY KEY DEFAULT uuid_v7()
```

Benefits:
- Time-sortable (first 48 bits = Unix timestamp in ms)
- Globally unique (no collisions)
- Good for indexes (lexicographic order = chronological order)
- Secure (remaining bits are random)

```
018d3f54-8c3a-7000-a234-56789abcdef0
^^^^^^^^^^^^^^^^ ← timestamp (2024-01-15 12:34:56.789)
                ^^^^^^^^^^^^^^^^^^^^ ← random
```

### SafeEnum Pattern

**Problem:** SQLAlchemy's default enum stores the Python name:

```python
class Status(enum.Enum):
    ACTIVE = "active"
    PAUSED = "paused"

# Database stores: "ACTIVE" (the Python name)
```

If you rename the enum:

```python
class Status(enum.Enum):
    RUNNING = "active"  # Renamed
    PAUSED = "paused"

# Database has "ACTIVE", Python doesn't recognize it → breaks!
```

**Solution: SafeEnum stores the value:**

```python
class Status(SafeEnum):
    ACTIVE = "active"
    PAUSED = "paused"

# Database stores: "active" (the value)
```

Now you can safely rename:

```python
class Status(SafeEnum):
    RUNNING = "active"  # Value is still "active"
    PAUSED = "paused"

# Database has "active", maps to Status.RUNNING ✓
```

### Cascade Deletes

When a parent record is deleted, what happens to children?

**Options:**

1. `ON DELETE CASCADE` - Delete children
2. `ON DELETE SET NULL` - Set foreign key to NULL
3. `ON DELETE RESTRICT` - Prevent deletion if children exist

**Our choices:**

```sql
-- User deleted → delete all their refresh tokens
user_id UUID REFERENCES users(id) ON DELETE CASCADE

-- User deleted → delete all their reports
researcher_id UUID REFERENCES users(id) ON DELETE CASCADE

-- Program deleted → delete all reports
program_id UUID REFERENCES programs(id) ON DELETE CASCADE

-- Report deleted → set duplicate_of_id to NULL
duplicate_of_id UUID REFERENCES reports(id) ON DELETE SET NULL
```

**Why CASCADE for reports?**
- When a program is deleted, its reports become orphaned
- No point keeping reports for deleted programs
- Alternative: Soft delete programs (`deleted_at`)

**Why SET NULL for duplicates?**
- If original report is deleted, duplicates can stand alone
- Don't cascade delete (duplicate might be wrong)

### Lazy Loading Disabled

SQLAlchemy's default behavior:

```python
user = await session.get(User, user_id)
programs = user.programs  # ← Implicit query! (N+1 problem)
```

**Problem:** Each access triggers a database query

**Solution:** Set `lazy="raise"`

```python
class User(Base):
    programs: Mapped[list[Program]] = relationship(
        back_populates="company",
        lazy="raise",  # ← Raise error on lazy load
    )

# Now you must explicitly load:
stmt = select(User).options(selectinload(User.programs))
user = await session.execute(stmt)
```

Forces developers to think about query efficiency.

---

## Indexes and Performance

### When to Index

**Index columns that are:**
1. Used in WHERE clauses (`WHERE email = ?`)
2. Used in JOIN conditions (`ON users.id = reports.researcher_id`)
3. Used for uniqueness (`UNIQUE INDEX`)
4. Used for sorting (`ORDER BY created_at`)

**Don't index:**
- Columns with low cardinality (`is_active` - only 2 values)
- Columns rarely queried
- Small tables (< 1000 rows)

### Our Indexes

```sql
-- Unique indexes (also enforce uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_programs_slug ON programs(slug);

-- Foreign key indexes (for JOINs)
CREATE INDEX idx_reports_program_id ON reports(program_id);
CREATE INDEX idx_reports_researcher_id ON reports(researcher_id);
CREATE INDEX idx_comments_report_id ON comments(report_id);

-- Filter indexes (for WHERE clauses)
CREATE INDEX idx_programs_status ON programs(status);
CREATE INDEX idx_reports_status ON reports(status);
```

### Query Performance

**Slow query:**
```python
# Gets all reports, then filters in Python
reports = await session.execute(select(Report))
active_reports = [r for r in reports if r.status == "new"]
```

**Fast query:**
```python
# Filters in database (uses index)
stmt = select(Report).where(Report.status == ReportStatus.NEW)
reports = await session.execute(stmt)
```

**EXPLAIN ANALYZE:**

```sql
EXPLAIN ANALYZE
SELECT * FROM reports WHERE status = 'new';

-- Good: Index Scan using idx_reports_status
-- Bad: Seq Scan on reports
```

---

## Migrations

### Why Alembic?

- Version control for database schema
- Automatic migration generation
- Rollback support
- Team collaboration (everyone has same schema)

### Creating Migrations

```bash
# 1. Modify a model
class User(Base):
    bio: Mapped[str | None] = mapped_column(Text, default=None)  # Added

# 2. Generate migration
just migration "Add user bio field"

# 3. Review generated migration
# alembic/versions/20240115_add_user_bio.py
def upgrade():
    op.add_column('users', sa.Column('bio', sa.Text(), nullable=True))

def downgrade():
    op.drop_column('users', 'bio')

# 4. Apply migration
just migrate head
```

### Migration Best Practices

1. **Always review generated migrations** - Alembic may not detect everything
2. **Test rollback** - Ensure `downgrade()` works
3. **Add indexes in separate migrations** - Can take time on large tables
4. **Use migrations for data changes** - Not just schema

**Example data migration:**

```python
def upgrade():
    # Add column
    op.add_column('users', sa.Column('reputation_score', sa.Integer(), default=0))

    # Backfill existing users
    op.execute("UPDATE users SET reputation_score = 0 WHERE reputation_score IS NULL")

    # Make NOT NULL
    op.alter_column('users', 'reputation_score', nullable=False)
```

---

## Common Queries

### Get Active Programs

```python
stmt = (
    select(Program)
    .where(Program.status == ProgramStatus.ACTIVE)
    .order_by(Program.created_at.desc())
)
programs = await session.execute(stmt)
```

### Get User's Reports

```python
stmt = (
    select(Report)
    .where(Report.researcher_id == user_id)
    .order_by(Report.created_at.desc())
)
reports = await session.execute(stmt)
```

### Get Report with Comments (Eager Loading)

```python
from sqlalchemy.orm import selectinload

stmt = (
    select(Report)
    .where(Report.id == report_id)
    .options(
        selectinload(Report.comments),
        selectinload(Report.attachments),
    )
)
report = await session.execute(stmt)
```

### Count Reports by Status

```python
from sqlalchemy import func

stmt = (
    select(Report.status, func.count(Report.id))
    .where(Report.program_id == program_id)
    .group_by(Report.status)
)
results = await session.execute(stmt)
```

### Find Duplicate Reports

```python
stmt = (
    select(Report)
    .where(Report.duplicate_of_id == original_report_id)
)
duplicates = await session.execute(stmt)
```

---

## Conclusion

Key takeaways:

1. **UUID v7** - Time-sortable, globally unique IDs
2. **SafeEnum** - Store enum values, not names
3. **Indexes** - Index foreign keys, unique columns, filter columns
4. **Migrations** - Version control for database schema
5. **Relationships** - Use `lazy="raise"` to prevent N+1 queries
6. **Cascade Deletes** - Think about what happens when parent is deleted

For more information:
- System architecture: [ARCHITECTURE.md](./ARCHITECTURE.md)
- Design patterns: [PATTERNS.md](./PATTERNS.md)
- Security features: [SECURITY.md](./SECURITY.md)
- Hands-on tutorial: [GETTING-STARTED.md](./GETTING-STARTED.md)