AR-002 — Database and stack (LOCKED)

Promoted from Action Required card AR-002. This is the decision of record.

I actually agree with the recommendation, but I would make it more enterprise-grade. The recommendation says: > "One database, no new vendor." I agree with no new vendor, but I would not put everything into a single schema forever. That's manageable today, but five years from now you'll regret it. Instead, I'd use one PostgreSQL server with multiple schemas. You still have one database instance, one backup strategy, one monitoring setup, but clear separation of concerns. --- # AR-002 — Database & Technology Stack (LOCKED) Status: APPROVED Priority: CRITICAL --- # Architecture Philosophy Astra follows a Single Infrastructure Strategy. There will be: * One VPS Provider (Contabo) * One PostgreSQL Instance * One Backend API * One Authentication Layer * One Monitoring Stack No unnecessary vendors. No duplicate databases. No vendor lock-in. --- # Infrastructure Overview text Contabo VPS Cluster │ ┌──────────────────────┼──────────────────────┐ │ │ │ Unity Game Server Aurion Backend ProjectOS │ │ │ └────────────── PostgreSQL Server ────────────┘ │ Multiple Schemas Everything runs inside your infrastructure. --- # Technology Stack ## Game Engine Unity 6 LTS --- ## Multiplayer Unity Netcode for GameObjects Dedicated Server --- ## Backend Node.js TypeScript Fastify Reason * Extremely fast * Lightweight * Excellent TypeScript support * Easy to scale --- ## ORM Prisma Reason * Strong typing * Reliable migrations * Easy schema management * Excellent developer experience --- ## Database PostgreSQL Hosted on Contabo. No Supabase. --- ## Cache Redis Used for: * Match queues * Session storage * Leaderboards * Rate limiting * Temporary gameplay state Never used as permanent storage. --- ## Reverse Proxy Caddy Handles: * HTTPS * TLS * Domain routing * Compression * Static assets --- ## File Storage Self-hosted object storage. Examples: * MinIO * S3-compatible storage Stores: * Cosmetics * Images * Icons * Asset previews * Event artwork Database stores references only. --- # PostgreSQL Design One PostgreSQL instance. Multiple schemas. Example: text postgres ├── astra_game ├── astra_projectos ├── astra_telemetry ├── astra_liveops └── public Each schema has a single responsibility. --- # Schema Responsibilities ## astra_game Core gameplay. Tables: * Players * Inventory * Cosmetics * Progression * Purchases * Hero Mastery * Weapon Mastery --- ## astra_projectos Project management. Tables: * Assets * Tasks * Documents * Risks * Milestones --- ## astra_telemetry High-volume analytics. Tables: * Match Events * FPS * Crash Logs * Device Stats * Gameplay Metrics Designed for heavy writes. --- ## astra_liveops Runtime configuration. Tables: * Events * Store Rotation * Seasons * News * Banners * Remote Configuration --- # Authentication Authentication handled by Unity Gaming Services. Backend trusts Unity-issued tokens. Flow: text Player Login ↓ Unity Authentication ↓ JWT Token ↓ Aurion Backend Validation ↓ Session Created No passwords stored by Astra. --- # API Architecture REST API For: * Profile * Inventory * Store * Cosmetics * Progression WebSocket For: * ProjectOS * Admin Dashboard * Live updates Gameplay communication stays on NGO, not REST. --- # Deployment Everything runs in Docker. Containers: text Game Server Backend API PostgreSQL Redis Caddy ProjectOS Monitoring Each service is isolated. --- # Monitoring Prometheus Metrics collection. Grafana Dashboards. Tracks: * CPU * RAM * Database * Network * Match Count * Active Players --- # Backup Strategy Daily PostgreSQL backup. Weekly full server snapshot. Monthly off-site encrypted backup. Backups tested regularly. --- # Security * TLS everywhere. * Database not publicly exposed. * Firewall blocks direct PostgreSQL access. * Prisma uses least-privilege database roles. * Secrets stored in environment variables. * API rate limiting enabled. --- # Development Workflow Development ↓ Local Docker ↓ Git ↓ CI ↓ Staging Server ↓ Production Never deploy directly to production. --- # Why Not Supabase? Supabase is an excellent product. However, Astra already owns its infrastructure. Advantages of staying on Contabo: * No vendor lock-in. * Predictable costs. * Full database control. * Unlimited custom schemas. * Easier integration with ProjectOS. * Better long-term scalability. --- # Future Scaling As Astra grows: * Multiple game servers. * Read replicas for PostgreSQL (if required). * Separate telemetry database if write volume becomes significant. * CDN for static assets. * Kubernetes only when operational complexity is justified. Do not over-engineer before player numbers demand it. --- # 🔒 Final Technology Stack | Layer | Technology | | ----------------- | ------------------------------ | | Game Engine | Unity 6 LTS | | Multiplayer | Unity Netcode for GameObjects | | Dedicated Servers | Unity Dedicated Server (Linux) | | Backend | Node.js + Fastify + TypeScript | | ORM | Prisma | | Database | PostgreSQL (Contabo) | | Cache | Redis | | Reverse Proxy | Caddy | | File Storage | MinIO (S3-compatible) | | Monitoring | Prometheus + Grafana | | Containers | Docker | | Version Control | GitHub | | ProjectOS | Next.js + React + PostgreSQL | --- # One recommendation I'd make beyond the original proposal I would not use the public schema for Astra's application data at all. Instead, create clearly separated schemas such as: * game * projectos * telemetry * liveops This gives you the benefits of a single PostgreSQL instance while keeping data organized, improving security through schema-level permissions, and making future maintenance much easier. It also allows Prisma to target specific schemas cleanly without introducing another database server or vendor.