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-focused sites with server rendering. Use Next.js (TypeScript) for interactive marketplace 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              ← PRIMARY FOR ALL APPS
├── Main site           ← bened.works
├── Rental platform     ← trailers.bened.works
├── Support system      ← support.bened.works
├── Praxis              ← This site
└── Future apps

TimescaleDB             ← TIME-SERIES SPECIALIST
├── TradeCraft          ← Stock/crypto candles (100M+ rows)
└── Research Archive    ← Document corpus metadata

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. All active apps now on PostgreSQL/TimescaleDB ✅
3. Legacy MySQL instances phased out

ADR-003: Centralized Secret Management

Context

Multiple applications need database credentials, API keys, and secrets. Each could manage its own secrets independently, or they could follow a centralized pattern.

Decision

Centralize secrets in a single secure location on the server. All apps load from this one source of truth. Never committed to version control.

Consequences

• Good: One place to update credentials — rotate a key once, every app picks it up
• Good: One place to secure with file permissions
• Good: Consistent pattern across all apps
• Bad: Apps share a secrets namespace (mitigated by server access control)
• Bad: Can't run apps in isolation without the full secret set

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 shares the same SQL dialect as PostgreSQL — same queries, same tools, just optimized for time-series workloads.

Consequences

• Good: Backtests run in seconds, not minutes
• Good: Can scale to billions of rows
• Bad: Another database instance to manage and monitor
• 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

User uploads, document archives, 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.