004: Storage Backend Decisions

Status

Accepted

Context

ProxyWhirl needs persistent storage for:

1. Proxy Pool State: Active proxies with metadata (health, performance, geographic data)
2. Circuit Breaker State: Failure tracking across application restarts
3. Cache Entries: Multi-tier cache persistence (L2 JSONL, L3 SQLite)
4. Analytics Data: Long-term proxy performance history
5. Configuration: User-defined proxy pools and settings

Requirements:

• Durability: Data survives application crashes and restarts
• Performance: Fast reads for proxy selection (<10ms)
• Queryability: Filter by health, source, geography, protocol
• Scalability: Support 100K+ proxies
• Portability: Single-file deployment, no external dependencies
• ACID Guarantees: Transactional updates for consistency
• Concurrent Access: Multiple processes reading/writing
• Schema Evolution: Easy migrations for new features
• Encryption: Secure credential storage

Key tradeoffs:

• SQLite vs PostgreSQL: Simplicity vs scalability
• File-based vs Database: Simplicity vs queryability
• Synchronous vs Asynchronous: Simplicity vs performance
• WAL vs DELETE journal: Portability vs write performance

Decision

We chose SQLite with multiple storage backends for different use cases:

Primary Storage: SQLite (SQLiteStorage)

Technology: SQLite 3.x via aiosqlite + SQLModel ORM

Use Cases:

• Primary proxy pool storage
• Circuit breaker state persistence
• L3 cache tier
• Analytics database

Key Characteristics:

class SQLiteStorage:
    def __init__(self, filepath: Path):
        self.engine = create_async_engine(
            f"sqlite+aiosqlite:///{filepath}",
            echo=False
        )

Schema Design (ProxyTable):

• 50+ columns covering all proxy metadata
• Indexed columns: country_code, source, health_status
• JSON columns: tags_json, metadata_json, error_types_json
• Full health monitoring fields (Feature 006)
• Response time metrics (EMA, percentiles, stddev)
• Error tracking by type (timeout, SSL, HTTP codes)

Journal Mode: DELETE (default) for single-file portability

• Avoids -wal/-shm sidecar files when copying or archiving the database
• Runtime pool default: ./proxywhirl.db (gitignored; populated by proxywhirl fetch)

Async Operations:

async def save(self, proxies: list[Proxy]) -> None:
    async with AsyncSession(self.engine) as session:
        # Delete + insert pattern for upsert
        await session.exec(delete(ProxyTable).where(...))
        for proxy in proxies:
            session.add(ProxyTable(...))
        await session.commit()

Advantages:

• Zero-configuration (no server process)
• Single-file deployment
• ACID transactions
• SQL query capabilities
• Excellent read performance
• Async I/O via aiosqlite
• Mature ecosystem (SQLModel, SQLAlchemy)

Limitations:

• Write concurrency (single writer at a time)
• Large database size (>1GB) can degrade performance
• No network access (local only)

Secondary Storage: File-Based (FileStorage)

Technology: JSON with Fernet encryption

Use Cases:

• Portable proxy exports
• Backup/restore
• Configuration files
• Testing snapshots

Key Characteristics:

class FileStorage:
    def __init__(self, filepath: Path, encryption_key: Optional[bytes] = None):
        self._cipher = Fernet(encryption_key) if encryption_key else None

    async def save(self, proxies: list[Proxy]) -> None:
        # Atomic write via temp file + rename
        data = [proxy.model_dump(mode="json") for proxy in proxies]
        json_str = json.dumps(data, indent=2)
        if self._cipher:
            content = self._cipher.encrypt(json_str.encode())
        # Write to temp, then rename (atomic)
        temp_path.replace(self.filepath)

Advantages:

• Human-readable (JSON)
• Easy debugging and inspection
• No dependencies (stdlib only)
• Credential encryption support
• Atomic writes (temp + rename)

Limitations:

• No querying (load all to memory)
• Poor performance for large datasets
• No concurrent writes
• No partial updates

Cache Storage: JSONL Sharding (DiskCacheTier)

Technology: Newline-delimited JSON with file locking

Use Cases:

• L2 cache tier (warm cache)
• Append-only logs
• Bulk imports/exports

Key Characteristics:

class DiskCacheTier:
    def _get_shard_path(self, key: str) -> Path:
        shard = key[:2]  # First 2 chars of key hash
        return self.cache_dir / f"shard_{shard}.jsonl"

    def put(self, key: str, entry: CacheEntry) -> bool:
        with portalocker.Lock(shard_path, "r+", timeout=5) as f:
            # Read, modify, rewrite atomically
            entries = [json.loads(line) for line in f]
            entries = [e for e in entries if e["key"] != key]
            entries.append(new_entry)
            f.seek(0)
            f.truncate()
            for e in entries:
                f.write(json.dumps(e) + "\n")

Advantages:

• Sharding reduces file size (256 shards)
• Atomic operations via portalocker
• Append-friendly format
• Easy log rotation

Limitations:

• Rewrite entire shard on updates
• No indexing (linear scan)
• File locking limits concurrency

Schema Definitions

ProxyTable (126 columns):

class ProxyTable(SQLModel, table=True):
    # Primary/Secondary Keys
    url: str = Field(primary_key=True)
    id: Optional[str] = None

    # Core Fields
    protocol: Optional[str] = None
    username: Optional[str] = None  # Plain text (app-level encryption)
    password: Optional[str] = None

    # Health Status (16 fields)
    health_status: str = "unknown"
    last_success_at: Optional[datetime] = None
    consecutive_failures: int = 0
    # ... 13 more health fields

    # Request Tracking (6 fields)
    requests_started: int = 0
    requests_completed: int = 0
    total_requests: int = 0
    # ... 3 more request fields

    # Response Time Metrics (9 fields)
    average_response_time_ms: Optional[float] = None
    ema_response_time_ms: Optional[float] = None
    response_time_p50_ms: Optional[float] = None
    # ... 6 more response fields

    # Error Tracking (9 fields)
    error_types_json: Optional[str] = None
    timeout_count: int = 0
    ssl_error_count: int = 0
    # ... 6 more error fields

    # Geographic (11 fields)
    country_code: Optional[str] = Field(index=True)
    region: Optional[str] = None
    latitude: Optional[float] = None
    # ... 8 more geo fields

    # Source Metadata (7 fields)
    source: str = Field(default="user", index=True)
    source_url: Optional[str] = None
    fetch_timestamp: Optional[datetime] = None
    # ... 4 more source fields

CacheEntryTable:

class CacheEntryTable(SQLModel, table=True):
    key: str = Field(primary_key=True)
    proxy_url: str
    username_encrypted: Optional[bytes] = None
    password_encrypted: Optional[bytes] = None
    source: str = Field(index=True)
    expires_at: float = Field(index=True)
    health_status: str = Field(default="unknown", index=True)
    # ... health monitoring fields

CircuitBreakerStateTable:

class CircuitBreakerStateTable(SQLModel, table=True):
    proxy_id: str = Field(primary_key=True)
    state: str = Field(index=True)  # closed, open, half_open
    failure_window_json: str = "[]"
    next_test_time: Optional[float] = None
    # ... configuration fields

Consequences

Positive

1. Zero Dependencies:

   • SQLite bundled with Python (no server setup)
   • Single-file deployment
   • No network configuration

2. Performance:

   • Indexed queries: <5ms for filtered selects
   • Batch writes: 1000 proxies in <50ms
   • Async I/O prevents blocking event loop

3. ACID Guarantees:

   • Transactional updates
   • No partial writes
   • Crash recovery

4. Queryability:

   • SQL filtering by any field
   • Aggregations (COUNT, AVG, etc.)
   • Joins (circuit breaker + proxy state)

5. Schema Evolution:

   • SQLModel generates migrations
   • CREATE TABLE IF NOT EXISTS
   • ALTER TABLE ADD COLUMN for new fields

6. CI Export Workflow:

   • CI builds a fresh proxywhirl.db during fetch/validate, then exports lists
   • Committed artifacts: docs/proxy-lists/ and mirrored web assets (not the DB)
   • Community access via exported text/JSON lists and the docs site

7. Credential Security:

   • FileStorage: Fernet encryption
   • CacheEntryTable: BLOB encryption
   • ProxyTable: App-level encryption (SecretStr)

8. Multiple Backends:

   • SQLite for primary storage
   • JSON for exports/backups
   • JSONL for cache tier
   • Each optimized for use case

Negative

1. Write Concurrency:

   • SQLite serializes writes
   • Concurrent readers OK, single writer
   • Mitigated by async I/O and batching

2. Database Size:

   • 100K proxies ≈ 50-100 MB
   • Large DBs (>1GB) degrade performance
   • Mitigated by TTL-based expiration

3. No Network Access:

   • Single-process only
   • No distributed deployments
   • Mitigated by file-based replication

4. Schema Inflation:

   • ProxyTable has 126 columns
   • Many nullable fields
   • Sparse data in JSON metadata
   • Mitigated by SQLite's efficient NULL handling

5. Migration Complexity:

   • Manual column additions required
   • No automatic migration framework
   • Mitigated by CREATE IF NOT EXISTS pattern

6. Git Database Tradeoffs:

   • DELETE journal slower than WAL
   • Database updates cause large git diffs
   • Mitigated by infrequent updates (6 hours)

Alternatives Considered

PostgreSQL:

• Better write concurrency
• Full-featured SQL
• Network access
• Requires server setup (deployment complexity)
• Overkill for single-process use case
• Rejected: Violates zero-dependency requirement

Redis:

• Excellent read/write performance
• Pub/sub for distributed coordination
• Requires server process
• No SQL query capabilities
• Rejected: Not needed for current scale

MongoDB:

• Flexible schema (JSON documents)
• Horizontal scaling
• Requires server process
• No ACID transactions (older versions)
• Rejected: Unnecessary complexity

Pure JSON Files:

• Simplest implementation
• Human-readable
• No querying without loading to memory
• No concurrent writes
• Rejected: Insufficient for 100K+ proxies

Pickle:

• Fast serialization
• Python-specific
• Not human-readable
• No querying
• Security risks
• Rejected: Not portable

CSV:

• Human-readable
• Universal format
• Poor for nested data (metadata)
• No concurrent writes
• No indexing
• Rejected: Insufficient features

LevelDB/RocksDB:

• Excellent write performance
• Key-value only (no SQL)
• C++ dependency
• Rejected: Overkill, no query support

Implementation Details

File Structure

proxywhirl/
├── storage.py           # SQLiteStorage, FileStorage
├── cache/
│   ├── tiers.py        # DiskCacheTier, SQLiteCacheTier
│   └── models.py       # CacheEntryTable schema
└── migrations.py        # Schema migration helpers

Storage Initialization

SQLite:

storage = SQLiteStorage("proxywhirl.db")
await storage.initialize()  # Create tables

proxies = [Proxy(...), ...]
await storage.save(proxies)

loaded = await storage.load()
filtered = await storage.query(source="user", health_status="healthy")

File:

storage = FileStorage("proxies.json", encryption_key=key)
await storage.save(proxies)
loaded = await storage.load()

Upsert Pattern

Delete + Insert (vs INSERT OR REPLACE):

async def save(self, proxies: list[Proxy]):
    urls = [p.url for p in proxies]
    await session.exec(delete(ProxyTable).where(ProxyTable.url.in_(urls)))
    for proxy in proxies:
        session.add(ProxyTable(...))
    await session.commit()

Rationale:

• Explicit delete ensures old data removed
• Avoids foreign key constraint issues
• Clear transaction boundaries

Index Strategy

Indexed Columns:

• ProxyTable.country_code - Geo-filtering (GeoTargetedStrategy)
• ProxyTable.source - Source filtering (query API)
• CacheEntryTable.expires_at - TTL cleanup
• CacheEntryTable.health_status - Health filtering
• CircuitBreakerStateTable.state - State queries

Non-Indexed Columns:

• All other fields (rarely queried)
• Metadata JSON (application-level filtering)
• Timestamps (except expires_at)

Rationale:

• Each index adds write overhead
• Only index frequently queried columns
• JSON fields indexed at application level

CI Proxy List Export

CI/CD Pipeline (.github/workflows/generate-proxies.yml):

- name: Fetch proxies
  run: uv run proxywhirl fetch --timeout 10 --concurrency 2000

- name: Export proxy lists
  run: uv run proxywhirl export

- name: Commit lists
  run: |
    git add docs/proxy-lists/ web/public/proxy-lists/
    git commit -m "chore: Update proxy lists"
    git push

DELETE Journal Mode:

# In SQLite connection
PRAGMA journal_mode=DELETE;  # Not WAL

Benefits:

• Community access to up-to-date proxy lists without bloating the repo
• No external API required for published list downloads
• Reproducible exports from the same fetch pipeline

Drawbacks:

• Large git repository size
• Slow git operations with binary diffs
• Merge conflicts require special handling

References

• Implementation: /Users/ww/dev/projects/proxywhirl/proxywhirl/storage.py
• Cache Tiers: /Users/ww/dev/projects/proxywhirl/proxywhirl/cache/tiers.py
• Schema Models: /Users/ww/dev/projects/proxywhirl/proxywhirl/cache/models.py
• Migrations: /Users/ww/dev/projects/proxywhirl/proxywhirl/migrations.py
• Tests: /Users/ww/dev/projects/proxywhirl/tests/unit/test_storage.py

Notes

Performance Benchmarks

From test suite (100K proxies):

• SQLite Save: 50ms (2000 proxies/sec)
• SQLite Load: 100ms (1M proxies/sec)
• SQLite Query (indexed): <5ms
• SQLite Query (full scan): 50ms
• File Save: 200ms (500 proxies/sec)
• File Load: 150ms (667 proxies/sec)
• JSONL Shard Write: 10ms (100 entries/sec)

Design Rationale

Why SQLite over PostgreSQL?

• Single-process use case doesn't need multi-user concurrency
• Zero-configuration deployment
• Sufficient performance for 100K proxies
• Portable (single file)

Why Async SQLite?

• Prevents blocking event loop
• Scales with concurrent requests
• Non-blocking I/O for disk operations

Why Multiple Storage Backends?

• Different use cases have different requirements
• SQLite for primary storage (queryability)
• JSON for exports (portability)
• JSONL for cache (append-friendly)

Why Git-Tracked Database?

• Democratizes access to proxy data
• No API server required
• Version control for analytics
• Community contribution model

Why DELETE Journal over WAL?

• WAL creates -shm and -wal files
• Lock files cause git tracking issues
• DELETE mode is single-file
• Performance tradeoff acceptable for analytics DB

Future Enhancements

1. PostgreSQL Backend (optional):

   • For multi-process deployments
   • Connection pooling
   • Advanced SQL features
   • Seamless migration path

2. S3/Cloud Storage:

   • Backup/restore to cloud
   • Distributed access
   • Versioned snapshots

3. Compression:

   • ZSTD compression for large databases
   • Transparent decompression
   • Reduce git repository size

4. Partitioning:

   • Separate active vs historical proxies
   • Archive old data to separate DB
   • Improve query performance

5. Read Replicas:

   • Multiple read-only copies
   • Distribute query load
   • Eventual consistency

6. Schema Versioning:

   • Alembic migrations
   • Automatic version detection
   • Rollback support