Architectural Decisions

Why things are the way they are. Every decision has context, tradeoffs, and consequences.

ADR-001: PHP for Content Sites, Next.js for SaaS

Context

Multiple projects need to be built. Some are content-focused (documentation, case management). Others require complex user interactions (rental marketplace with search, booking, payments).

Decision

Use PHP for content-heavy sites with forms and server rendering. Use Next.js (TypeScript) for interactive SaaS applications.

Consequences

• Good: PHP sites have no build step, instant deploys, simple debugging
• Good: Next.js provides component reuse, real-time updates, better Stripe/React integration
• Bad: Two different patterns to maintain
• Bad: Docker complexity for Next.js deployments

Alternatives Considered

• All Next.js: Overkill for content sites, adds Docker complexity everywhere
• All PHP: Difficult to build modern interactive UIs
• Laravel: More structure than needed, steeper learning curve

ADR-002: PostgreSQL as Primary Database

Context

The ecosystem was using MySQL by default for PHP applications. However, MySQL has significant limitations that cause real problems:

• Weak JSON support — can't efficiently query inside JSON fields
• No array types — must serialize/deserialize manually
• Silent data truncation — inserts "succeed" but lose data
• Limited window functions — complex analytics need workarounds
• No partial indexes — can't optimize specific query patterns
• CHECK constraints were ignored until MySQL 8.0.16

Meanwhile, PostgreSQL was already being used for the trailer rental platform and TimescaleDB (which is PostgreSQL) was used for TradeCraft.

Decision

Standardize on PostgreSQL for all new applications. MySQL is legacy-only (Moodle).

Database Architecture

PostgreSQL (port 5432)     ← PRIMARY FOR ALL NEW APPS
├── praxis                 ← This site
├── trailer_rental         ← Rental platform  
├── case_portal            ← Migrate when ready
└── future apps

TimescaleDB (port 5433)    ← PRODUCTION SERVER (time-series)
└── tradecraft             ← Stock/crypto candles

MySQL (port 3306)          ← LEGACY ONLY
└── moodle                 ← Can't change (Moodle core)

Why PostgreSQL Wins

Consequences

• Good: One database engine to learn deeply
• Good: JSONB enables flexible schemas where needed
• Good: Same syntax as TimescaleDB (it IS PostgreSQL)
• Good: Better tooling (pgAdmin, psql, explain analyze)
• Bad: Need php-pgsql extension (installed: php8.2-pgsql)
• Bad: Slight syntax differences from MySQL (quotes, booleans)

Migration Path

1. New apps use PostgreSQL from day one ✅
2. Case Portal migrates when there's bandwidth
3. Moodle stays on MySQL forever (can't change)

ADR-003: Centralized .env at /var/www/private/

Context

Multiple applications need database credentials, API keys, and secrets. Each could have its own .env file, or they could share one.

Decision

Single .env file at /var/www/private/.env. All PHP apps load from this location.

Consequences

• Good: One place to update credentials
• Good: One place to secure (file permissions)
• Good: Consistent pattern across all apps
• Bad: Apps see each other's secrets (mitigated by server access control)
• Bad: Can't run apps in isolation without full .env

ADR-004: TimescaleDB for Trading Data

Context

TradeCraft stores stock/crypto candles: 100+ symbols × 2 years × 5-minute intervals = 100M+ rows. Queries need to filter by symbol and time range, then aggregate.

Decision

Use TimescaleDB (PostgreSQL extension) instead of MySQL for candle storage.

Why TimescaleDB?

• Hypertables: Automatic time-based partitioning
• Compression: 90%+ storage reduction for historical data
• Query speed: Sub-second on 100M rows with proper indexes
• SQL compatible: Same PDO code, just change the DSN

Note: TimescaleDB runs on the production TradeCraft server (port 5433), not this sandbox.

Consequences

• Good: Backtests run in seconds, not minutes
• Good: Can scale to billions of rows
• Bad: Another database to manage (port 5433)
• Bad: Team needs to learn PostgreSQL syntax differences

ADR-005: Keycloak for Centralized SSO

Context

Multiple apps need user authentication. Options: build auth into each app, or use a central identity provider.

Decision

Deploy Keycloak at auth.bened.works. All apps authenticate via OIDC.

Consequences

• Good: Single sign-on — log in once, access all apps
• Good: Battle-tested security (don't roll your own auth)
• Good: Supports social login, MFA, user management UI
• Bad: Keycloak is complex, Java-based, resource-hungry
• Bad: OAuth flow adds complexity to app code

ADR-006: Backblaze B2 + Cloudflare for File Storage

Context

Evidence files, user uploads, and static assets need to be stored somewhere. Options: local disk, AWS S3, or alternatives.

Decision

Use Backblaze B2 for storage with Cloudflare CDN in front for caching and fast delivery.

Why Not S3?

• B2 is 1/4 the price of S3
• S3-compatible API (same code works)
• Cloudflare → B2 egress is free (Bandwidth Alliance)

Consequences

• Good: Extremely cheap storage (~$5/TB/month)
• Good: Fast delivery via Cloudflare edge
• Good: Files don't fill up VPS disk
• Bad: Slightly more complex than local files
• Bad: Depends on external service availability

ADR-007: Docker for Next.js Only

Context

Docker provides isolation and reproducibility but adds complexity. Some apps benefit more than others.

Decision

Run PHP apps directly on the VPS. Run Next.js apps in Docker containers.

Rationale

• PHP is simple: just files + PHP-FPM. No build step needed.
• Next.js requires Node.js, has a build step, needs process management.
• Docker makes Next.js deployment reproducible and isolated.
• Don't add Docker complexity where it's not needed.