# UmbraDatastore

Configuration, deployment scripts, and specifications for the UmbraTrans data layer.

## Overview

This repository contains the infrastructure-as-code for the UmbraTrans PostgreSQL database cluster and related data services. It uses CloudNativePG (CNPG) for PostgreSQL high availability and NFS CSI for shared storage.

## Components

### Database (PostgreSQL + CNPG)
- **High Availability**: 3-node cluster (1 primary + 2 replicas)
- **Connection Pooling**: PgBouncer (transaction mode)
- **Backups**: Continuous WAL archiving + daily base backups to S3
- **Monitoring**: Prometheus PodMonitor with custom queries
- **Extensions**: uuid-ossp, citext, pgcrypto

### Shared Storage (NFS)
- **Productions**: Source book data and translation runs (`/mnt/umbra-data/productions`)
- **Artifacts**: Content-addressed audio/EPUB/PDF outputs (`/mnt/umbra-data/artifacts`)
- **Covers**: Cover images (`/mnt/umbra-data/covers`)
- **Audio**: Master audio files (`/mnt/umbra-data/audio`)
- **Backups**: Database backup staging (`/mnt/umbra-data/backups`)

### Schema
The database schema includes:
- **Identity**: users, organizations, sessions, credentials, audit_events
- **Projects**: projects (per-tenant schemas), format_metadata
- **Compute**: compute_nodes, compute_devices, model_registry, node_models, jobs, job_events
- **Artifacts**: artifacts, job_artifacts, project_source_refs
- **Publish/Byline**: publishers, authors, project_publishers, project_authors

## Quick Start

### Prerequisites
- Kubernetes cluster (v1.28+)
- NFS server accessible from cluster (default: `192.168.123.14:/mnt/umbra-data`)
- S3-compatible storage for backups
- cert-manager + nginx-ingress (for web UI)

### Deploy

```bash
# Make script executable
chmod +x scripts/deploy-database.sh

# Run deployment (includes building and pushing database container)
./scripts/deploy-database.sh
```

The script will:
1. Build and push database container image
2. Install CNPG operator
3. Create namespace and secrets
4. Deploy PostgreSQL cluster
5. Run migrations
6. Create initial admin user

### Build Database Container Manually

If you need to build and push the database container separately:

```bash
# Build the image
docker build -t 192.168.123.18:3001/umbragit/umbradatastore:latest \
       -t 192.168.123.18:3001/umbragit/umbradatastore:16.4 \
       -f Dockerfile \
       /path/to/UmbraDatastore

# Push to registry (requires Docker daemon configured for insecure registry)
# Add to /etc/docker/daemon.json:
# { "insecure-registries": ["192.168.123.18:3001"] }
# Then restart Docker: systemctl restart docker

docker push 192.168.123.18:3001/umbragit/umbradatastore:latest
docker push 192.168.123.18:3001/umbragit/umbradatastore:16.4
```

### Manual Steps

If you prefer manual deployment:

```bash
# 1. Install CNPG operator
kubectl apply -f https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.24.0/releases/cnpg-1.24.0.yaml

# 2. Install NFS CSI driver
kubectl apply -f https://raw.githubusercontent.com/kubernetes-csi/csi-driver-nfs/v4.7.0/deploy/example/nfs-driver.yaml

# 3. Create namespace
kubectl create namespace umbratrans

# 4. Create secrets (see scripts/deploy-database.sh for details)

# 3. Deploy cluster
kubectl apply -k k8s/base
```

## Configuration

### Database Cluster (`k8s/base/01-postgresql.yaml`)
Key settings:
- Instances: 3 (1 primary + 2 replicas)
- Storage: 50Gi on NFS
- Resources: 1-2Gi memory, 0.5-2 CPU
- PgBouncer: 2 replicas, transaction pooling

### NFS Storage (`k8s/base/02-nfs-storage.yaml`)
Update the NFS server IP:
```yaml
parameters:
  server: 192.168.123.14  # <-- Your NFS server IP
  share: /mnt/umbra-data
```

### Monitoring (`k8s/base/07-monitoring-secrets.yaml`)
- ServiceMonitors for Prometheus
- Custom PostgreSQL queries
- Alert rules for cluster health

## Operations

### Connect to Database
```bash
# Via port-forward
kubectl port-forward -n umbratrans svc/umbratrans-db-rw 5432:5432
psql -h localhost -U umbratrans -d umbratrans

# Direct (internal)
psql -h umbratrans-db-rw.umbratrans.svc -U umbratrans -d umbratrans
```

### Check Cluster Status
```bash
kubectl get cluster umbratrans-db -n umbratrans
kubectl get pods -n umbratrans -l postgresql=umbratrans-db
```

### View Logs
```bash
# Primary
kubectl logs -n umbratrans -l postgresql=umbratrans-db,role=primary -f

# PgBouncer
kubectl logs -n umbratrans -l app=umbratrans-db-pgbouncer -f
```

### Backup/Restore
```bash
# Manual backup
kubectl apply -f - <<EOF
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
  name: manual-backup-$(date +%s)
  namespace: umbratrans
spec:
  cluster: umbratrans-db
EOF

# List backups
kubectl get backups -n umbratrans

# Restore to new cluster
kubectl apply -f - <<EOF
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: umbratrans-db-restored
  namespace: umbratrans
spec:
  instances: 1
  storage:
    size: 50Gi
  bootstrap:
    recovery:
      source: umbratrans-db
      backup:
        name: manual-backup-1234567890
EOF
```

### Scale Resources
```bash
# Scale cluster (instances)
kubectl patch cluster umbratrans-db -n umbratrans \
  -p '{"spec":{"instances":5}}' --type=merge

# Scale PgBouncer
kubectl patch cluster umbratrans-db -n umbratrans \
  -p '{"spec":{"proxy":{"pgBouncer":{"replicas":4}}}}' --type=merge
```

## Secrets Management

All sensitive data is stored in Kubernetes Secrets:
- `umbratrans-db-credentials` - Database user/password/DSN
- `umbratrans-pgbouncer-admin` - PgBouncer admin credentials
- `umbratrans-secrets` - Token pepper, etc.
- `umbratrans-smtp` - SMTP credentials
- `umbratrans-backup-credentials` - S3 backup credentials

Update placeholder values after deployment:
```bash
kubectl patch secret umbratrans-smtp -n umbratrans \
  -p '{"data":{"password":"'$(echo -n "real-password" | base64)'"}}'
```

## Documentation

- [Deployment Guide](docs/DEPLOYMENT_GUIDE.md) - Full deployment instructions
- [Datastore Specification](docs/DATASTORE_SPEC.md) - Complete schema and contracts
- [Integration Spec](docs/INTEGRATION_SPEC.md) - UmbraAudio/UmbraFormat integration

## Architecture

```
┌─────────────────────────────────────────────────────────────┐
│                    KUBERNETES CLUSTER                        │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────┐  │
│  │ umbratrans   │  │ umbratrans   │  │  umbratrans      │  │
│  │ web (API)    │  │ worker       │  │  worker          │  │
│  └──────┬───────┘  └──────┬───────┘  └────────┬─────────┘  │
│         │                 │                    │            │
│         └─────────────────┼────────────────────┘            │
│                           ▼                                 │
│  ┌──────────────────────────────────────────────────────┐  │
│  │              POSTGRESQL CLUSTER (CNPG)                │  │
│  │  Primary ──── Replica 1                              │  │
│  │      │                                                  │  │
│  │      └──── Replica 2                                  │  │
│  │                                                         │  │
│  │  PgBouncer (pool) ──► Primary                         │  │
│  └──────────────────────────────────────────────────────┘  │
│                           │                                 │
│         ┌─────────────────┼─────────────────┐              │
│         ▼                 ▼                 ▼              │
│  ┌────────────┐    ┌────────────┐    ┌────────────┐      │
│  │ Productions│    │ Artifacts  │    │   Covers   │      │
│  │  (NFS)     │    │  (NFS)     │    │  (NFS)     │      │
│  └────────────┘    └────────────┘    └────────────┘      │
└─────────────────────────────────────────────────────────────┘
```

## Related Repositories

- **UmbraTrans** - Main application (API, UI, workers)
- **UmbraAudio** - Audio processing node
- **UmbraFormat** - EPUB/PDF formatting node
- **UmbraTools** - Core libraries and tools

## License

Apache-2.0