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

# Database-First Spider Management

> Why spiders live in the database, not in Python files

scrapai stores spiders as **database rows**, not Python files. This architectural choice enables powerful management patterns impossible with file-based scrapers.

## The Traditional Approach: File-Based Spiders

In a typical Scrapy project:

```python Traditional Scrapy Project theme={null}
project/
├── spiders/
│   ├── bbc_spider.py
│   ├── cnn_spider.py
│   ├── nytimes_spider.py
│   ├── guardian_spider.py
│   └── ... 96 more files
├── settings.py
└── scrapy.cfg
```

**Problems at scale**:

<CardGroup cols={2}>
  <Card title="No Central Inventory" icon="list">
    Which spiders exist? Grep the filesystem. Which are active? Check each file.
  </Card>

  <Card title="Hard to Batch Update" icon="pen-to-square">
    Change DOWNLOAD\_DELAY across 100 spiders? Edit 100 files or write a script.
  </Card>

  <Card title="No Metadata" icon="tag">
    When was this spider created? By whom? For what project? Add comments and hope.
  </Card>

  <Card title="Code Drift" icon="code-fork">
    5 developers write spiders in 5 different styles. No consistency, harder to review.
  </Card>
</CardGroup>

## The scrapai Approach: Database-First

Three tables: **spiders** (metadata, domains, URLs), **spider\_rules** (URL patterns, callbacks), **spider\_settings** (key-value configs). One generic `DatabaseSpider` class loads any config at runtime.

## Benefits of Database-First

### 1. Central Inventory

```bash theme={null}
./scrapai spiders list --project news
```

Or direct SQL: `SELECT name, active, created_at FROM spiders WHERE project = 'news'`. No filesystem traversal, no parsing Python files.

### 2. Batch Updates

```sql theme={null}
-- Increase delay for all news spiders
UPDATE spider_settings
SET value = '2'
WHERE key = 'DOWNLOAD_DELAY'
  AND spider_id IN (SELECT id FROM spiders WHERE project = 'news');
```

With files: edit 100 Python files. With database: one SQL query.

### 3. Rich Metadata

Every spider tracks creation time, update time, project, and activity status:

```sql theme={null}
-- Count spiders per project
SELECT project, COUNT(*) FROM spiders GROUP BY project;

-- Find stale spiders (not updated in 90 days)
SELECT name, updated_at FROM spiders WHERE updated_at < NOW() - INTERVAL '90 days';
```

### 4. Import/Export as Data

```bash theme={null}
./scrapai spiders export bbc_co_uk --project news > bbc_spider.json
./scrapai spiders import bbc_spider.json --project news-backup
```

Spiders are JSON configs. Export, share, version control, backup/restore like any data file.

### 5. Consistency and Validation

All configs validated via Pydantic: strict naming (`^[a-zA-Z0-9_-]+$`), HTTP/HTTPS URLs only, private IPs blocked. No malformed configs reach the database.

## Querying the Database

scrapai provides a safe SQL query interface:

```bash Read-Only Queries theme={null}
# List all spiders
./scrapai db query "SELECT name, project, active FROM spiders"

# Count items per spider
./scrapai db query "SELECT spider_id, COUNT(*) FROM scraped_items GROUP BY spider_id"

# Find spiders with Cloudflare enabled
./scrapai db query "SELECT s.name FROM spiders s JOIN spider_settings ss ON s.id = ss.spider_id WHERE ss.key = 'CLOUDFLARE_ENABLED' AND ss.value = 'true'"
```

<Warning>
  **UPDATE/DELETE queries require confirmation** via `--confirm` flag with row count to prevent accidental data loss.

  ```bash theme={null}
  ./scrapai db query "DELETE FROM spiders WHERE name = 'test_spider'" --confirm 1
  ```
</Warning>

## Real-World Patterns

### Pattern 1: Fleet Health Check

Run test crawls on all spiders monthly:

```bash Test All Spiders theme={null}
# Get active spiders
./scrapai db query "SELECT name FROM spiders WHERE active = true AND project = 'news'" > active_spiders.txt

# Test each spider (5 sample URLs)
while read spider; do
  echo "Testing $spider..."
  ./scrapai crawl "$spider" --project news --limit 5
done < active_spiders.txt

# Check for extraction failures
./scrapai db query "SELECT spider_id, COUNT(*) FROM scraped_items WHERE scraped_at > NOW() - INTERVAL '1 day' GROUP BY spider_id HAVING COUNT(*) = 0"
```

### Pattern 2: Bulk Configuration Changes

```sql Rate Limit All Spiders theme={null}
-- Increase delay for aggressive sites
UPDATE spider_settings 
SET value = '3'
WHERE key = 'DOWNLOAD_DELAY' 
  AND spider_id IN (
    SELECT id FROM spiders 
    WHERE allowed_domains::text LIKE '%amazon%' 
       OR allowed_domains::text LIKE '%ebay%'
  );
```

### Pattern 3: Spider Versioning

Export before making changes:

```bash Backup Before Update theme={null}
# Export current config
./scrapai spiders export bbc_co_uk --project news > bbc_v1.json

# Make changes (via agent or manual edit)
# ... modify rules, settings, etc.

# Test new version
./scrapai crawl bbc_co_uk --project news --limit 10

# Rollback if needed
./scrapai spiders import bbc_v1.json --project news
```

### Pattern 4: Multi-Project Management

```bash Project Isolation theme={null}
# Create separate projects for different use cases
./scrapai spiders import news_spider.json --project production
./scrapai spiders import news_spider.json --project staging
./scrapai spiders import news_spider.json --project testing

# Each project has independent configs and data
./scrapai crawl news_spider --project production
./scrapai crawl news_spider --project testing --limit 5
```

## PostgreSQL vs SQLite

**SQLite** (default): Single-user, simple deployment, auto-enabled WAL mode. Good for \< 100 spiders.

**PostgreSQL** (production): Multi-user teams, 100+ spiders, high concurrency. Migrate with:

```bash theme={null}
# Update .env with PostgreSQL URL
./scrapai db migrate
./scrapai db transfer sqlite:///scrapai.db
```

## Next Steps

<CardGroup cols={2}>
  <Card title="Spider Schema" icon="database" href="/api/spider-schema">
    Detailed schema reference for Spider, SpiderRule, and SpiderSetting
  </Card>

  <Card title="CLI Reference" icon="terminal" href="/cli/spiders">
    Commands for spider management: list, import, export, delete
  </Card>
</CardGroup>