# Database Schema

Database table structure for CiteFlow - designed for AI bot tracking and referral analytics.

## Overview

CiteFlow uses PostgreSQL with Prisma ORM. The schema follows a **three-layer architecture** for optimal performance and scalability:

| Layer | Tables | Retention | Purpose |
|-------|--------|-----------|---------|
| **Raw** | CrawlerLog | 30 days | Debugging raw crawler requests |
| **Analytics** | BotVisit, Referral | Permanent | Business facts for reporting |
| **Aggregation** | Page | Permanent | Aggregated page statistics |

## Core Tables

### Site

Tracked websites.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier (cuid) |
| userId | String | Owner's user ID |
| domain | String | Website domain |
| createdAt | DateTime | Creation timestamp |
| updatedAt | DateTime | Update timestamp |

**Relations:**
- `pages` → Page[]
- `botVisits` → BotVisit[]
- `sessions` → Session[]
- `referrals` → Referral[]
- `crawlerLogs` → CrawlerLog[]

**Indexes:**
- `userId`
- `domain`

### Page (Core Entity)

Page discovery and performance metrics.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| siteId | String | Associated site |
| url | String | Page URL (unique per site) |
| title | String | Page title (optional) |
| firstSeenAt | DateTime | First discovered timestamp |
| lastSeenAt | DateTime | Last crawl timestamp |
| crawlCount | Int | Total AI bot crawls |
| referralCount | Int | Total AI referrals |
| lastCrawlBy | String | Last bot that crawled |
| isIndexed | Boolean | Marked as indexed by AI |

**Relations:**
- `site` → Site
- `botVisits` → BotVisit[]
- `referrals` → Referral[]

**Indexes:**
- `siteId, url` (unique)
- `siteId`
- `crawlCount`
- `isIndexed`

### BotVisit (Analytics Layer)

AI bot visit records.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| siteId | String | Associated site |
| pageId | String | Crawled page |
| botType | BotType | AI bot type |
| vendor | String | Vendor (openai, anthropic, etc.) |
| category | AICategory | AI category |
| userAgent | String | Full user agent |
| status | Int | HTTP status code |
| createdAt | DateTime | Visit timestamp |

**Relations:**
- `site` → Site
- `page` → Page

**Indexes:**
- `siteId, createdAt`
- `botType`
- `category`

### Referral (Analytics Layer)

AI referral traffic records.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| siteId | String | Associated site |
| pageId | String | Landing page |
| sessionId | String | Visitor session |
| source | String | AI source (chatgpt, claude, etc.) |
| landingPage | String | Landing page URL |
| userAgent | String | User agent |
| referrer | String | Original referrer |
| createdAt | DateTime | Visit timestamp |

**Relations:**
- `site` → Site
- `page` → Page
- `session` → Session

**Indexes:**
- `siteId, createdAt`
- `source`

### Session

Visitor sessions.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| siteId | String | Associated site |
| source | AISource | AI source |
| landingPage | String | First page visited |
| country | String | Visitor country |
| device | DeviceType | Device type |
| startedAt | DateTime | Session start |
| lastSeenAt | DateTime | Last activity |
| eventCount | Int | Number of events |

**Relations:**
- `site` → Site
- `events` → Event[]
- `referrals` → Referral[]

### Event (Transactional)

User interaction events.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| siteId | String | Associated site |
| sessionId | String | Session ID |
| eventType | EventType | Event type |
| url | String | Page URL |
| createdAt | DateTime | Event timestamp |

**Relations:**
- `site` → Site
- `session` → Session

### CrawlerLog (Raw Layer)

**IMPORTANT: This is the DEBUG/RAW layer.**
- Dashboard should NEVER query this table directly
- Retention: 30 days (auto-cleanup)
- Use BotVisit for analytics queries

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| siteId | String | Associated site |
| category | AICategory | AI classification |
| vendor | String | Vendor ID |
| product | String | Product ID |
| bot | String | Bot name |
| url | String | Requested URL |
| userAgent | String | User agent |
| referer | String | Referrer |
| ipHash | String | Hashed IP |
| secFetchSite | String | Sec-Fetch-Site header |
| secFetchMode | String | Sec-Fetch-Mode header |
| country | String | Country |
| confidence | String | Detection confidence |
| matchedType | String | Detection method |
| status | Int | HTTP status |
| createdAt | DateTime | Timestamp |

**Relations:**
- `site` → Site

**Indexes:**
- `siteId, createdAt`
- `bot, createdAt`
- `category, createdAt`

### Subscription

User subscriptions.

| Field | Type | Description |
|-------|------|-------------|
| id | String | Unique identifier |
| userId | String | User ID |
| plan | PlanType | Subscription plan |
| stripeCustomerId | String | Stripe customer ID |
| status | String | Subscription status |
| monthlyEventLimit | Int | Event limit |
| siteLimit | Int | Site limit |
| retentionDays | Int | Data retention days |
| createdAt | DateTime | Creation timestamp |
| updatedAt | DateTime | Update timestamp |

**Plans:**
| Plan | Price | Features |
|------|-------|----------|
| free | $0 | 1 site, 30 days history |
| starter | $5/mo | 5 sites, 90 days history |
| pro | $9/mo | 10 sites, 365 days history, alerts |
| agency | $29/mo | 100 sites, API, white label |

## Enums

### BotType

```prisma
enum BotType {
  GPTBOT
  CLAUDEBOT
  PERPLEXITYBOT
  GOOGLE_EXTENDED
  GEMINI
  META_EXTERNAL_AGENT
  BYTEDANCE_BYTESPIDER
  UNKNOWN
}
```

### AICategory

```prisma
enum AICategory {
  AI_CRAWLER          // Training crawlers
  AI_SEARCH_CRAWLER   // Search engine AI crawlers
  AI_USER_FETCH       // User-initiated requests
  AI_REFERRAL         // Visitors from AI chat
  AI_BROWSER_AGENT    // Browser-based AI agents
  UNKNOWN_AI          // Unrecognized AI traffic
}
```

### AISource

```prisma
enum AISource {
  chatgpt
  perplexity
  gemini
  copilot
  claude
  google
  bing
  direct
  referral
  unknown
}
```

### DeviceType

```prisma
enum DeviceType {
  desktop
  mobile
  tablet
}
```

### EventType

```prisma
enum EventType {
  pageview
  session_start
  engagement
  route_change
}
```

### PlanType

```prisma
enum PlanType {
  free
  starter
  pro
  agency
}
```

## Migration Management

### Create Migration

```bash
npx prisma migrate dev --name description
```

### Apply Migrations (Production)

```bash
npx prisma migrate deploy
```

### Reset Database (Development Only)

```bash
npx prisma migrate reset --force
```

### CrawlerLog Cleanup

Automated cleanup job (30 days retention):

```bash
# Run manually
npm run cron:cleanup

# Cron job (daily at 2 AM)
0 2 * * * npm run cron:cleanup
```

## Query Best Practices

### ✅ Good: Use Analytics Tables

```sql
-- Dashboard queries should use these tables
SELECT * FROM BotVisit WHERE siteId = ?;
SELECT * FROM Referral WHERE siteId = ?;
SELECT * FROM Page WHERE siteId = ?;
```

### ❌ Bad: Query Raw Logs Directly

```sql
-- Avoid this for dashboard queries
SELECT * FROM CrawlerLog WHERE siteId = ?; -- Use for debugging only!
```

---

Back to [Documentation](./README.md)