# ADR-002: Use SQLite for Local Development

## Status
Accepted

## Context
For local development and testing, we needed a database solution that:
- Requires minimal setup and configuration
- Works well with Go's database drivers
- Supports the same SQL features as production databases
- Allows easy reset and recreation of test data
- Doesn't require external services running locally

## Decision
We chose SQLite as the default database for local development and testing environments.

## Consequences

### Positive
- Zero configuration - database is just a file
- Fast performance for local development workloads
- Easy to reset by deleting the database file
- Excellent Go driver support (mattn/go-sqlite3)
- Supports most SQL features we need
- Portable across different development machines
- No external dependencies or services to manage

### Negative
- Limited to single connection at a time (file locking)
- Not suitable for production multi-user scenarios
- Some advanced SQL features may not be available
- Different behavior compared to PostgreSQL in production

## Options Considered

### PostgreSQL
**Pros:**
- Production-grade database
- Excellent feature support
- Good Go driver support
- Consistent with production environment

**Cons:**
- Requires external service installation and configuration
- Higher resource usage
- More complex setup for new developers
- Overkill for simple local development

### MySQL
**Pros:**
- Popular and well-supported
- Good Go drivers available

**Cons:**
- Requires external service
- More complex setup
- Different SQL dialect than PostgreSQL

### In-memory databases (Redis, etc.)
**Pros:**
- Very fast
- No persistence needed for some tests

**Cons:**
- Limited query capabilities
- Not suitable for complex relational data
- Different data model than production

### No database (file-based storage)
**Pros:**
- Simple implementation
- No dependencies

**Cons:**
- Limited query capabilities
- No transaction support
- Hard to scale to complex data needs

## Rationale

SQLite provides the perfect balance of simplicity and functionality for local development. It requires zero setup - developers can just run the application and it works. The file-based nature makes it easy to reset test data by deleting the database file. While it differs from our production PostgreSQL database, it supports the same core SQL features needed for development and testing.

The main limitation is single-writer access, but this is acceptable for local development where typically only one developer is working with the database at a time. For integration tests that need concurrent access, we can use PostgreSQL or Redis.
