← back to Watches
database/DATABASE_ARCHITECTURE.md
619 lines
# Omega Watch Price History - Database Architecture
## Table of Contents
1. [Architecture Overview](#architecture-overview)
2. [Technology Stack](#technology-stack)
3. [Database Schema](#database-schema)
4. [Data Access Patterns](#data-access-patterns)
5. [Performance Optimization](#performance-optimization)
6. [Caching Strategy](#caching-strategy)
7. [Migration Guide](#migration-guide)
8. [Scalability Considerations](#scalability-considerations)
---
## Architecture Overview
### Design Philosophy
This database architecture implements:
- **Relational Model**: PostgreSQL for structured watch data with complex relationships
- **Repository Pattern**: Abstraction layer for data access
- **Caching Layer**: Redis for high-performance read operations
- **Service Layer**: Business logic with integrated caching
- **Partitioning**: Time-series partitioning for price history
- **Materialized Views**: Pre-calculated statistics for fast queries
### Architecture Layers
```
┌─────────────────────────────────────────────────────────────┐
│ API / Business Logic │
├─────────────────────────────────────────────────────────────┤
│ Service Layer │
│ (WatchService, Analytics) │
├─────────────────────────────────────────────────────────────┤
│ Cache Layer (Redis) │
│ TTL-based caching with invalidation │
├─────────────────────────────────────────────────────────────┤
│ Repository Pattern │
│ (WatchRepository, PriceHistoryRepository) │
├─────────────────────────────────────────────────────────────┤
│ Connection Pool (pg) │
│ 20 max connections, auto-reconnect │
├─────────────────────────────────────────────────────────────┤
│ PostgreSQL Database │
│ Partitioned tables, materialized views, indices │
└─────────────────────────────────────────────────────────────┘
```
---
## Technology Stack
### Core Components
| Component | Technology | Version | Purpose |
|-----------|-----------|---------|---------|
| Database | PostgreSQL | 14+ | Relational data storage |
| Cache | Redis | 6+ | Performance layer |
| ORM/Pool | node-postgres (pg) | 8.x | Connection pooling |
| Cache Client | ioredis | 5.x | Redis operations |
### Extensions Used
- `uuid-ossp`: UUID generation
- `pg_trgm`: Full-text search optimization
---
## Database Schema
### Core Tables
#### 1. Watches Table
Primary entity storing watch information.
```sql
CREATE TABLE watches (
id UUID PRIMARY KEY,
slug VARCHAR(255) UNIQUE NOT NULL,
model VARCHAR(255) NOT NULL,
series watch_series_enum NOT NULL,
reference VARCHAR(100) NOT NULL,
year_introduced INTEGER NOT NULL,
description TEXT,
detailed_description TEXT,
image_url VARCHAR(500),
is_active BOOLEAN DEFAULT true,
is_limited_edition BOOLEAN DEFAULT false,
limited_edition_count INTEGER,
search_vector tsvector, -- Auto-updated via trigger
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
```
**Indices:**
- `idx_watches_search` (GIN): Full-text search
- `idx_watches_series`: Filter by series
- `idx_watches_year`: Filter by year
- `idx_watches_active`: Active watches only
#### 2. Specifications Table
One-to-one relationship with watches.
```sql
CREATE TABLE specifications (
id UUID PRIMARY KEY,
watch_id UUID UNIQUE REFERENCES watches(id) ON DELETE CASCADE,
case_size VARCHAR(50),
case_material case_material_enum,
movement_calibre VARCHAR(100),
water_resistance VARCHAR(50),
-- ... additional specs
);
```
#### 3. Price History Table (PARTITIONED)
Time-series data partitioned by year for optimal performance.
```sql
CREATE TABLE price_history (
id UUID,
watch_id UUID REFERENCES watches(id),
year INTEGER NOT NULL,
price NUMERIC(12,2) NOT NULL,
condition condition_enum,
source VARCHAR(255),
currency CHAR(3) DEFAULT 'USD',
is_estimate BOOLEAN DEFAULT false,
confidence_level INTEGER,
PRIMARY KEY (id, year)
) PARTITION BY RANGE (year);
```
**Partitions:**
- `price_history_1950_1979`: 1950-1979
- `price_history_1980_1999`: 1980-1999
- `price_history_2000_2019`: 2000-2019
- `price_history_2020_2030`: 2020-2030
**Benefits:**
- Faster queries on specific time ranges
- Easier data archiving
- Better maintenance operations
#### 4. Features & Watch_Features
Many-to-many relationship for watch features.
```sql
CREATE TABLE features (
id UUID PRIMARY KEY,
name VARCHAR(100) UNIQUE NOT NULL,
category VARCHAR(50),
description TEXT
);
CREATE TABLE watch_features (
watch_id UUID REFERENCES watches(id),
feature_id UUID REFERENCES features(id),
PRIMARY KEY (watch_id, feature_id)
);
```
### Materialized Views
#### Watch Statistics View
Pre-calculated aggregations for fast queries.
```sql
CREATE MATERIALIZED VIEW watch_statistics AS
SELECT
w.id,
w.model,
COUNT(ph.id) as price_point_count,
MIN(ph.price) as lowest_price,
MAX(ph.price) as highest_price,
AVG(ph.price) as average_price,
-- Appreciation calculation
((MAX(ph.price) - MIN(ph.price)) / MIN(ph.price) * 100) as appreciation_percentage,
-- Annualized return
(POWER(MAX(ph.price) / MIN(ph.price), 1.0 / (MAX(ph.year) - MIN(ph.year))) - 1) * 100 as annualized_return_percentage
FROM watches w
LEFT JOIN price_history ph ON w.id = ph.watch_id
GROUP BY w.id;
```
**Refresh Strategy:**
- On-demand via `REFRESH MATERIALIZED VIEW CONCURRENTLY`
- Scheduled via cron (recommended: daily)
- After bulk data imports
### Triggers
#### 1. Auto-Update Timestamps
```sql
CREATE TRIGGER watches_update_timestamp
BEFORE UPDATE ON watches
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
```
#### 2. Auto-Update Search Vector
```sql
CREATE TRIGGER watches_search_vector_update
BEFORE INSERT OR UPDATE ON watches
FOR EACH ROW
EXECUTE FUNCTION update_watch_search_vector();
```
#### 3. Audit Trail
```sql
CREATE TRIGGER watches_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON watches
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_function();
```
---
## Data Access Patterns
### Repository Pattern
Each entity has a dedicated repository class:
```javascript
// WatchRepository
class WatchRepository {
async findAll(options = {}) { ... }
async findById(identifier) { ... }
async findByIdWithPriceHistory(identifier) { ... }
async search(searchTerm, options = {}) { ... }
async create(watchData) { ... }
async update(watchId, updateData) { ... }
}
// PriceHistoryRepository
class PriceHistoryRepository {
async findByWatchId(watchId, options = {}) { ... }
async calculateAppreciation(watchId, startYear, endYear) { ... }
async getPriceTrends(options = {}) { ... }
async create(priceData) { ... }
}
```
### Service Layer with Caching
Business logic layer integrates caching:
```javascript
class WatchService {
async getWatchById(watchId, includeHistory = false) {
const cacheKey = this.cache.generateKey('watch:full', watchId);
return await this.cache.getOrSet(
cacheKey,
async () => await this.watchRepo.findById(watchId),
this.cache.TTL.LONG
);
}
}
```
---
## Performance Optimization
### 1. Connection Pooling
```javascript
const pool = new Pool({
max: 20, // Maximum connections
min: 5, // Minimum connections
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
maxUses: 7500 // Recycle after 7500 uses
});
```
### 2. Query Optimization
**Use Indices:**
```sql
-- Full-text search
SELECT * FROM watches
WHERE search_vector @@ plainto_tsquery('english', 'speedmaster');
-- Filtered queries
SELECT * FROM watches
WHERE series = 'Speedmaster'
AND year_introduced >= 2000;
```
**Partition Pruning:**
```sql
-- Query only relevant partition
SELECT * FROM price_history
WHERE watch_id = 'uuid'
AND year BETWEEN 2000 AND 2020;
```
### 3. Materialized Views
```sql
-- Fast aggregation queries
SELECT * FROM watch_statistics
ORDER BY appreciation_percentage DESC
LIMIT 10;
```
### 4. Prepared Statements
All repository queries use parameterized queries:
```javascript
const result = await pool.query(
'SELECT * FROM watches WHERE id = $1',
[watchId]
);
```
---
## Caching Strategy
### Cache Hierarchy
| Level | Technology | TTL | Use Case |
|-------|-----------|-----|----------|
| L1 | In-Memory | 60s | Hot data (trending) |
| L2 | Redis | 5-30min | Watch details, stats |
| L3 | PostgreSQL | - | Source of truth |
### Cache Keys
```
omega:watch:{watchId} - Single watch
omega:watches:all:{params} - Watch list
omega:watch:stats:{watchId} - Statistics
omega:price-history:{watchId} - Price data
omega:trending:{days}:{limit} - Trending watches
omega:search:{term}:{params} - Search results
```
### TTL Configuration
```javascript
TTL = {
SHORT: 60, // 1 minute (trending, search)
MEDIUM: 300, // 5 minutes (lists)
LONG: 1800, // 30 minutes (details, stats)
VERY_LONG: 3600, // 1 hour (calculated metrics)
DAY: 86400 // 24 hours (static data)
}
```
### Cache Invalidation
```javascript
// Invalidate on write
async updateWatch(watchId, data) {
await watchRepo.update(watchId, data);
await cache.invalidateWatch(watchId); // Invalidate related keys
await cache.deletePattern('omega:watches:*'); // Clear lists
}
```
### Cache Warming
```javascript
// On application startup
async warmCache() {
const watches = await watchRepo.findAll({ limit: 100 });
await cache.warmCache(watches, 'watch', TTL.LONG);
}
```
---
## Migration Guide
### 1. Setup Database
```bash
# Run initialization script
cd database
chmod +x init.sh
./init.sh
```
### 2. Migrate JSON to PostgreSQL
```bash
# Run migration script
node database/migrate-json-to-postgres.js
```
**Migration Process:**
1. Creates watches from JSON
2. Extracts specifications
3. Parses features (many-to-many)
4. Migrates price history
5. Refreshes materialized views
### 3. Verify Migration
```sql
-- Check record counts
SELECT
(SELECT COUNT(*) FROM watches) as watches,
(SELECT COUNT(*) FROM price_history) as prices,
(SELECT COUNT(*) FROM features) as features;
-- Verify statistics
SELECT * FROM watch_statistics LIMIT 5;
```
---
## Scalability Considerations
### Horizontal Scaling
#### Read Replicas
```
Master (Write)
├── Replica 1 (Read)
├── Replica 2 (Read)
└── Replica 3 (Read)
```
**Configuration:**
```javascript
const masterPool = new Pool({ host: 'master.db' });
const replicaPool = new Pool({ host: 'replica.db' });
// Route queries
async function query(sql, params, { write = false } = {}) {
const pool = write ? masterPool : replicaPool;
return await pool.query(sql, params);
}
```
#### Partitioning Strategy
**Time-Based (Current):**
- Price history partitioned by year
- Easy to archive old data
- Optimal for time-range queries
**Future: Hash Partitioning**
```sql
-- For watch table if > 1M watches
CREATE TABLE watches
PARTITION BY HASH (id);
```
### Vertical Scaling
**Current Specs:**
- 2 CPU cores
- 4GB RAM
- 50GB SSD
**Recommended for Production:**
- 4-8 CPU cores
- 16-32GB RAM (for PostgreSQL shared_buffers)
- 200GB+ SSD with high IOPS
### Sharding (Future)
If data exceeds single-server capacity:
```
Shard 1: Speedmaster series
Shard 2: Seamaster series
Shard 3: Other series
```
### Database Optimization
```sql
-- PostgreSQL configuration (postgresql.conf)
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
work_mem = 64MB
max_connections = 100
max_wal_size = 2GB
```
---
## Monitoring & Maintenance
### Health Checks
```javascript
// Database health endpoint
GET /api/health/database
Response:
{
"database": {
"healthy": true,
"poolStats": {
"total": 20,
"idle": 15,
"waiting": 0
}
},
"cache": {
"healthy": true,
"connected": true
}
}
```
### Performance Monitoring
```sql
-- Slow queries
SELECT * FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
-- Index usage
SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;
-- Cache hit ratio
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))
as cache_hit_ratio
FROM pg_statio_user_tables;
```
### Maintenance Tasks
**Daily:**
- Refresh materialized views
- Clear old cache entries
**Weekly:**
- VACUUM ANALYZE tables
- Check index fragmentation
**Monthly:**
- Review slow query log
- Optimize queries
- Archive old data
---
## Backup Strategy
### Automated Backups
```bash
#!/bin/bash
# backup.sh
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/omega_watches"
# PostgreSQL backup
pg_dump omega_watches | gzip > "$BACKUP_DIR/omega_watches_$TIMESTAMP.sql.gz"
# Cleanup old backups (keep 30 days)
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete
```
**Schedule:**
```cron
0 2 * * * /root/Projects/watches/database/backup.sh
```
### Point-in-Time Recovery
Enable WAL archiving:
```sql
-- postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
```
---
## Security Best Practices
1. **Least Privilege:** Application user has limited permissions
2. **Parameterized Queries:** Prevent SQL injection
3. **SSL Connections:** Encrypt database traffic
4. **Regular Updates:** Keep PostgreSQL patched
5. **Audit Logging:** Track all database changes
---
## Conclusion
This database architecture provides:
- ✅ **Performance**: Caching, indices, partitioning
- ✅ **Scalability**: Read replicas, horizontal partitioning
- ✅ **Reliability**: Connection pooling, health checks
- ✅ **Maintainability**: Repository pattern, migrations
- ✅ **Analytics**: Materialized views, stored procedures
For questions or issues, refer to the individual component documentation or contact the development team.