# Fase 3: Long-Term Infrastructure Guide

## [ARC-04] API Versioning

### Current State
All routes are at `/api/[resource]` (unversioned).

### Migration Plan
When breaking changes are needed:

1. Create versioned route files:
   ```
   backend/src/routes/v1/invoices.js  ← copy current
   backend/src/routes/v2/invoices.js  ← new version
   ```

2. Register in server.js:
   ```javascript
   app.register(invoiceRoutesV1, { prefix: '/api/v1/invoices' })
   app.register(invoiceRoutesV2, { prefix: '/api/v2/invoices' })
   // Keep unversioned as alias for latest stable
   app.register(invoiceRoutesV1, { prefix: '/api/invoices' })
   ```

3. Deprecation header:
   ```javascript
   app.addHook('onSend', (req, reply, payload, done) => {
     if (req.url.startsWith('/api/v1/')) {
       reply.header('Sunset', 'Sat, 01 Jan 2028 00:00:00 GMT')
       reply.header('Deprecation', 'true')
     }
     done()
   })
   ```

### Timeline
- Implement when first breaking API change is needed
- Maintain v1 for minimum 6 months after v2 launch

---

## [DB-05] Connection Pooling (PgBouncer)

### Why
Prisma creates connection per request. Under load (100+ concurrent):
- PostgreSQL max_connections = 100 (default)
- Each connection uses ~10MB RAM

### Setup
1. Install PgBouncer:
   ```bash
   # Ubuntu/Debian
   sudo apt install pgbouncer
   
   # Windows: use official installer from pgbouncer.github.io
   ```

2. Configure `/etc/pgbouncer/pgbouncer.ini`:
   ```ini
   [databases]
   sarana_gemilang = host=127.0.0.1 port=5432 dbname=sarana_gemilang
   
   [pgbouncer]
   listen_port = 6432
   listen_addr = 127.0.0.1
   auth_type = md5
   pool_mode = transaction
   max_client_conn = 200
   default_pool_size = 20
   min_pool_size = 5
   reserve_pool_size = 5
   ```

3. Update `.env`:
   ```
   DATABASE_URL="postgresql://user:pass@localhost:6432/sarana_gemilang?pgbouncer=true"
   ```

4. Update Prisma datasource:
   ```prisma
   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
     // Reduce Prisma's own pool since PgBouncer manages it
     // connectionLimit = 5  ← set via URL param
   }
   ```

### Prisma-specific URL params
```
?pgbouncer=true&connection_limit=5&pool_timeout=10
```

---

## [DB-03] Archival Strategy

### When to Implement
- When `invoices` table exceeds 500K rows
- When `audit_log` exceeds 1M rows

### Strategy: Time-based archive tables

```sql
-- Create archive tables (identical schema)
CREATE TABLE invoices_archive (LIKE invoices INCLUDING ALL);
CREATE TABLE audit_log_archive (LIKE audit_log INCLUDING ALL);

-- Archive invoices older than 3 years
INSERT INTO invoices_archive
SELECT * FROM invoices
WHERE "tanggalInvoice" < NOW() - INTERVAL '3 years'
  AND status IN ('LUNAS', 'BATAL');

DELETE FROM invoices WHERE id IN (SELECT id FROM invoices_archive);

-- Archive audit logs older than 2 years  
INSERT INTO audit_log_archive
SELECT * FROM audit_log
WHERE "createdAt" < NOW() - INTERVAL '2 years';

DELETE FROM audit_log WHERE id IN (SELECT id FROM audit_log_archive);
```

### Automated Script
```javascript
// Run monthly via cron
// node scripts/archive.js
```

---

## [DB-01] Table Partitioning

### When to Implement
When `invoices` table exceeds 1M rows.

### Strategy: Range partition by year

```sql
-- Convert to partitioned table (requires pg 12+)
-- This is a one-time migration:

-- 1. Rename existing table
ALTER TABLE invoices RENAME TO invoices_old;

-- 2. Create partitioned table
CREATE TABLE invoices (LIKE invoices_old INCLUDING ALL)
  PARTITION BY RANGE (tahun);

-- 3. Create yearly partitions  
CREATE TABLE invoices_2024 PARTITION OF invoices
  FOR VALUES FROM (2024) TO (2025);
CREATE TABLE invoices_2025 PARTITION OF invoices
  FOR VALUES FROM (2025) TO (2026);
CREATE TABLE invoices_2026 PARTITION OF invoices
  FOR VALUES FROM (2026) TO (2027);

-- 4. Migrate data
INSERT INTO invoices SELECT * FROM invoices_old;

-- 5. Drop old table after verification
DROP TABLE invoices_old;
```

### Auto-create future partitions (cron job):
```sql
-- Run Jan 1 each year
DO $$
DECLARE
  y INT := EXTRACT(YEAR FROM NOW()) + 1;
BEGIN
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS invoices_%s PARTITION OF invoices FOR VALUES FROM (%s) TO (%s)',
    y, y, y + 1
  );
END $$;
```

---

## [MON-01] Sentry Error Tracking

### Setup
```bash
npm install @sentry/node
```

```javascript
// server.js
import * as Sentry from '@sentry/node'

Sentry.init({
  dsn: process.env.SENTRY_DSN,
  environment: process.env.NODE_ENV,
  tracesSampleRate: IS_PROD ? 0.1 : 1.0,
})

// Add Sentry error handler
app.setErrorHandler((error, req, reply) => {
  Sentry.captureException(error, {
    extra: {
      url: req.url,
      method: req.method,
      userId: req.user?.userId,
    },
  })
  // ... existing error handler
})
```

---

## [ARC-03] React Query Migration

### Why
- Current: Manual `useEffect` + `useState` for all API calls
- Problem: No caching, no refetch-on-focus, manual loading states
- Solution: React Query (TanStack Query) handles all of this

### Steps
1. `npm install @tanstack/react-query`
2. Wrap App with `QueryClientProvider`
3. Migrate page-by-page (start with read-heavy pages like Dashboard)
4. Example migration:
   ```jsx
   // Before
   const [data, setData] = useState([])
   useEffect(() => { btdAPI.list().then(setData) }, [])
   
   // After
   const { data } = useQuery({ queryKey: ['btd'], queryFn: btdAPI.list })
   ```

### Priority order
1. Dashboard (most read-heavy)
2. DaftarInvoice
3. DaftarBTD / DaftarBDN
4. MasterKlien / MasterTarif