Skip to content

PRJ2 Database Infrastructure

Date: 2026-01-09 Status: Accepted Context: PRJ2 student team database provisioning and initialization

Decision

Deploy a shared PostgreSQL instance serving all teams, with platform-controlled database initialization.

Architecture

graph TB
    classDef system fill:#438DD5,stroke:#3C7FC0,color:#fff
    classDef team fill:#85BBF0,stroke:#78A8D8,color:#000

    subgraph prj2sys ["prj2-system namespace"]
        pg["<b>PostgreSQL</b><br/>(Bitnami Helm)<br/>Databases: team_a, team_b, team_c, ..."]:::system
        pgadmin["<b>pgAdmin</b><br/>pgadmin.prod.fontysvenlo.dev"]:::system
        pgadmin -->|"connects to"| pg
    end

    subgraph teamns ["team-a namespace"]
        initdb["<b>init-db</b><br/>1. DROP DB<br/>2. CREATE DB<br/>3. RUN init.sql"]:::team
        initdb -->|"success"| backend["<b>Backend</b>"]:::team
        frontend["<b>Frontend</b>"]:::team
    end

    initdb -->|"creates schema"| pg
    backend -->|"queries"| pg

Components

Component Location Purpose
PostgreSQL prj2-system namespace Shared database server (Bitnami Helm)
pgAdmin prj2-system namespace Web UI for student database access
db-init image harbor.../platform/db-init Platform-controlled init container
Team credentials Central Secret in prj2-system One secret with all team passwords

Database Initialization Modes

The platform db-init image supports two modes, auto-detected based on folder structure:

Mode Trigger Behavior Use Case
Reset (default) db/init.sql exists DROP → CREATE → run init.sql Learning, rapid iteration
Migrate (opt-in) db/migrations/ exists CREATE IF NOT EXISTS → Flyway migrate Data preservation, industry patterns

Student folder structure: 

db/
├── init.sql              ← Reset mode (simple path)
└── migrations/           ← Migration mode (advanced path)
    ├── V1__create_schema.sql
    └── V2__add_products.sql

If both exist, migrations/ takes precedence.

Credential Distribution

Instructor provides credentials via Canvas:

  1. Provisioning script generates team database users + passwords
  2. Script outputs a credential table
  3. Instructor posts in Canvas (per-team assignment notes)
  4. Students use for pgAdmin and backend configuration

Security Boundaries

Actor Can Access Cannot Access
Platform admin All databases, admin credentials -
Student team Their database via pgAdmin/app Other team databases, admin creds
Init container Admin creds (at runtime only) Other team init.sql
Backend app Team database credentials Admin credentials

Rationale

Why shared PostgreSQL?

  • Resource efficiency: One instance serves all teams
  • Simplified management: Single point of administration
  • Course isolation: Separate instance per course (prj2-system, prj3-system)

Why platform-controlled init container?

  • Security: Students can't modify the init logic
  • Isolation: Teams can only affect their own database
  • Predictability: Platform guarantees clean state behavior

Why dual-mode (reset vs migrate)?

  • Default (reset): Simple, predictable, ideal for learning
  • Opt-in (migrate): Teaches industry-standard patterns (Flyway)
  • Detection-based: No config needed—just add migrations/ folder to switch
  • Security maintained: Platform still controls execution

Why Canvas for credentials?

  • Simple: Works with existing infrastructure
  • Secure enough: Per-team credentials, no shared secrets
  • Future path: Can add self-service portal or SSO later

Alternatives Considered

Per-team PostgreSQL instances

  • ❌ Resource waste for educational scale
  • ❌ Complex management overhead

Student-controlled init scripts in Dockerfile

  • ❌ Security risk: students could affect other teams
  • ❌ No guaranteed clean state

Self-service credential portal

  • ❌ Additional infrastructure to build
  • ✓ May add later as enhancement