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

┌────────────────────────────────────────────────────────┐
│                   prj2-system namespace                │
│                                                        │
│  ┌─────────────────────────────────────────────────┐   │
│  │         PostgreSQL (Bitnami Helm chart)         │   │
│  │                                                 │   │
│  │  Databases:                                     │   │
│  │  ┌─────────┐ ┌─────────┐ ┌─────────┐            │   │
│  │  │ team_a  │ │ team_b  │ │ team_c  │  ...       │   │
│  │  └─────────┘ └─────────┘ └─────────┘            │   │
│  └─────────────────────────────────────────────────┘   │
│                                                        │
│  ┌─────────────────────────────────────────────────┐   │
│  │              pgAdmin (web UI)                   │   │
│  │      https://pgadmin.prod.fontysvenlo.dev       │   │
│  └─────────────────────────────────────────────────┘   │
│                                                        │
└────────────────────────────────────────────────────────┘

┌────────────────────────────────────────────────────────┐
│                   team-a namespace                     │
│                                                        │
│  ┌────────────────┐                                    │
│  │ init-db        │  (init container)                  │
│  │ 1. DROP DB     │  - Platform-controlled image       │
│  │ 2. CREATE DB   │  - Reads team's init.sql           │
│  │ 3. RUN init.sql│                                    │
│  └───────┬────────┘                                    │
│          │ success                                     │
│          ▼                                             │
│  ┌────────────────┐    ┌────────────────┐              │
│  │    Backend     │    │   Frontend     │              │
│  └────────────────┘    └────────────────┘              │
│                                                        │
└────────────────────────────────────────────────────────┘

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

Implementation

See prj2-database-infrastructure.md for detailed implementation plan and tasks.