Database Schema

This document is the canonical reference for the PostgreSQL schema managed by Prisma. It covers every model, field, constraint, and index. Cross-references to data flow and API behaviour are noted inline.

---

1. Schema Configuration

Prisma 7 separates schema definition from connection configuration. The schema file (prisma/schema.prisma) defines the provider and generator. The connection URL lives in prisma.config.mjs.

Schema file

datasource db {
    provider = "postgresql"
}

generator client {
    provider = "prisma-client"
    output   = "../src/generated/prisma"
}

Connection configuration (prisma.config.mjs)

import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';

export default defineConfig({
    schema: 'prisma/schema.prisma',
    migrations: { path: 'prisma/migrations' },
    datasource: { url: env('POSTGRES_URL') },
});

This file is read by the Prisma CLI for migrations and introspection. The dotenv/config import loads .env for local CLI usage; in Docker, POSTGRES_URL is injected as a real environment variable.

Runtime client (src/db/db.js)

Prisma 7 requires a JavaScript driver adapter instead of the Rust query engine. The project uses @prisma/adapter-pg (which bundles pg internally):

import { PrismaClient } from '@/generated/prisma/client';
import { PrismaPg } from '@prisma/adapter-pg';

const prismaClientSingleton = () => {
    const adapter = new PrismaPg({ connectionString: process.env.POSTGRES_URL });
    return new PrismaClient({ adapter });
};

The singleton pattern caches the client on globalThis.prismaGlobal to survive Next.js hot reloads in development.

---

2. Entity Relationship Diagram

erDiagram
    h1_season ||--o{ h1_status : "OneSeasonToManyStatus"
    h1_season ||--o{ h1_statistic : "OneSeasonToManyStatistic"
    h1_season ||--o{ h1_event : "OneSeasonToManyEvents"
    h1_event ||--o{ h1_event_progress : "OneEventToManyProgress"

    h1_season {
        String   id                 PK
        DateTime last_updated       "nullable"
        Int      season             UK
        Int[]    introduction_order
        Int[]    points_max
        Int      season_duration
    }

    h1_event {
        String id       PK
        Int    season   FK
        String type
        Int    event_id
        Int    region
        String status
    }

    h1_status {
        String id     PK
        Int    season FK
        Int    enemy
        Int    bucket
        Int    time
        Int    points
        String status
    }

    h1_statistic {
        String id     PK
        Int    season FK
        Int    enemy
        Int    bucket
        Int    time
        BigInt kills
        BigInt deaths
    }

    h1_event_progress {
        String id       PK
        String type
        Int    event_id FK
        Int    bucket
        Int    time
        Int    points
    }

    User ||--o{ Account : "accounts"
    User ||--o{ Session : "sessions"
    User ||--o| Settings : "settings"
    User ||--o{ Review : "reviews"
    User ||--o{ ApiKey : "apiKeys"

    User {
        String   id            PK
        String   username      UK
        String   email         UK
        Boolean  emailVerified
        String   role
        Boolean  banned
        DateTime createdAt
        DateTime updatedAt
    }

    Account {
        String   id         PK
        String   userId     FK
        String   accountId
        String   providerId
        DateTime accessTokenExpiresAt
        DateTime refreshTokenExpiresAt
    }

    Session {
        String   id        PK
        String   token     UK
        String   userId    FK
        DateTime expiresAt
        String   ipAddress
        String   userAgent
    }

    Verification {
        String   id         PK
        String   identifier
        String   value
        DateTime expiresAt
    }

    Settings {
        String userId PK-FK
        Json   settings
    }

    Review {
        String  id        PK
        String  authorId  FK
        Boolean published
    }

    ApiKey {
        String  id      PK
        String  hash    UK
        String  userId  FK
        Boolean enabled
    }

    App {
        String id             PK
        String version
        Int    active_season
    }

    worker_heartbeat {
        String   worker_type PK
        DateTime last_beat
        Int      poll_duration_ms "nullable"
        String   last_error "nullable"
        DateTime started_at
        DateTime updated_at
    }

    push_subscription {
        Int      id         PK "autoincrement"
        String   endpoint   UK
        String   keys_p256dh
        String   keys_auth
        DateTime created_at
    }

The former rebroadcast_status and rebroadcast_snapshot tables have been removed. The /api/h1/rebroadcast endpoint now reconstructs the raw API response format on demand from the normalized h1_* tables.

Data Lifecycle

graph TD
    subgraph Write["WRITE PATH"]
        API["Official API Response"] --> SEASON["h1_season<br/><small>unconfirmed</small>"]
        SEASON --> CHILDREN["h1_event<br/>h1_status<br/>h1_statistic"]
        CHILDREN --> PROGRESS["h1_event_progress"]
        PROGRESS --> CONFIRM["h1_season<br/><small>confirmed</small>"]
    end

    subgraph Read["READ PATH"]
        LIVE_Q["/api/h1/live"] --> SEASON_R["h1_season + h1_event + h1_status"]
        RB_Q["/api/h1/rebroadcast"] --> SEASON_R2["Reconstructed on demand<br/>from h1_* tables"]
        CAMP_Q["/api/h1/campaign"] --> SEASON_R
        ARCHIVE["/archives"] --> ALL["All h1_* tables"]
    end

    style Write fill:#0f1a0f,stroke:#22c55e,color:#4ade80
    style Read fill:#1e293b,stroke:#3b82f6,color:#60a5fa

---

3. Game Data Models

h1_season

The root anchor for all game data. Every normalised game table points back to this row via season (the integer game season number, not the surrogate id). Season-level metadata (introduction_order, points_max, season_duration) is inlined directly on this model rather than stored in separate tables.

Indexes:

Relations (outbound):

Design note: Child tables reference season (Int) rather than the surrogate id (UUID). This keeps foreign keys human-readable, avoids UUID joins in queries that filter by season number, and matches the season integer used in the official API response.

---

h1_event

A unified table that holds both attack and defend events in a single model. The type column discriminates between the two event kinds, eliminating the need for UNION queries across separate tables.

Constraints and indexes:

Relations:

---

h1_event_progress

Bucketed time-series data for individual events. Each row captures the points of an event within a time bucket, enabling progress-over-time visualisations. Links to h1_event via the compound (type, event_id) key. Uses the same tumbling-window bucket-upsert pattern as h1_status and h1_statistic — points_max is constant per event and lives on h1_event, so only points (the progression signal) is tracked here.

Constraints and indexes:

Relations:

---

h1_status

Bucketed campaign timeseries. Each row captures the campaign state for one enemy faction within a time bucket. Uses a tumbling-window bucket-upsert pattern: bucket = floor(poll_time / BUCKET_SIZE) * BUCKET_SIZE. Within an active bucket window, subsequent polls UPDATE the existing row with latest values. At a bucket boundary, a new row is INSERTed. BUCKET_SIZE is configurable via env var (default 900 = 15 min).

Constraints and indexes:

---

h1_statistic

Bucketed per-faction statistics timeseries. Each row captures 11 statistical fields for one enemy faction within a time bucket. Uses the same tumbling-window bucket-upsert pattern as h1_status. Fields that are per-season state (like season_duration) or derivable from other tables (like event counts) have been moved out.

Constraints and indexes:

BigInt fields: kills, deaths, accidentals, shots, and hits use BigInt (PostgreSQL BIGINT) rather than Int. Helldivers cumulative counts for an active season easily exceed the 32-bit integer maximum of ~2.1 billion. When reading these values in JavaScript, Prisma returns them as native BigInt primitives — serialisation to JSON requires explicit conversion (e.g., .toString() or a custom serialiser).

---

4. Rebroadcast (Removed)

The former rebroadcast_status and rebroadcast_snapshot tables have been dropped. They previously cached the raw, unmodified JSON responses from the official Helldivers API as a fast-path for the /api/h1/rebroadcast endpoint.

The rebroadcast endpoint now reconstructs the official API response format on demand from the normalized h1_* tables. This eliminates the storage duplication and the need to keep raw JSON in sync with normalised data.

---

5. Auth and User Models

These models implement BetterAuth with the Prisma adapter, plus application-specific extensions. Authentication is OAuth-only (Discord and GitHub providers). See Authentication for architecture details.

User

Central user record. Extended beyond the BetterAuth baseline with username, role, banned, and application-specific relations.

Relations:

---

Account

BetterAuth OAuth account record. Links a user to an OAuth provider. Multiple providers per user are supported (e.g., both Discord and GitHub linked to one account).

Indexes: @@index([userId])

Unlike the previous NextAuth.js schema, userId no longer has a @unique constraint. BetterAuth allows multiple OAuth accounts per user, enabling users to link both Discord and GitHub.

---

Session

BetterAuth database session. Each row represents an active session with metadata.

Indexes: @@index([userId])

---

Verification

BetterAuth verification tokens for email verification and other confirmation flows.

Constraints: @@unique([identifier, value]) — composite uniqueness on identifier + value pair.

---

Settings

One-to-one with User. Stores all user preferences as a single JSON blob. Uses userId as both the primary key and the FK, enforcing the one-to-one relationship at the database level.

---

Review

Blog-style user-submitted content. Currently unpublished by default, suggesting a moderation workflow.

---

ApiKey

Application API keys for external consumers. A random UUID is generated as the key, SHA-256 hashed for storage. The plaintext key is shown once at creation and never stored, making the visible field the only partially-readable remnant.

Security note: SHA-256 is used as a one-way hash for key lookup. The key space is a random UUID (128 bits of entropy), which provides the actual security. The hash allows O(1) database lookups without storing the raw key. Validation logic is in src/shared/utils/api/validateApiKey.mjs.

---

6. Push Notifications

push_subscription

Stores Web Push API subscriptions for server-initiated push notifications. Subscriptions are anonymous (no user link required). Created via POST /api/notifications/subscribe, removed on unsubscribe or when the push service returns 410/404 (stale endpoint).

Relationships: None (standalone table, not linked to User).

Used by: src/update/pushNotifier.mjs (queries all subscriptions on event transitions), src/app/api/notifications/subscribe/route.js (CRUD).

---

7. App Configuration Model

App

A single-row configuration table for application-level state. Intended to have exactly one row at runtime.

Several fields are commented out in the schema (is_db_initialized, last_updated_status, last_updated_season, settings), indicating planned but deferred functionality.

---

8. Worker Health

worker_heartbeat

Single-row-per-worker health tracking table. The worker thread upserts its row on every poll cycle, enabling health checks and diagnostics. The worker_type string serves as both PK and discriminator (e.g., "cron").

Relationships: None (standalone table, not linked to game data or users).

Used by: public/workers/cron.js (upserts on each poll cycle), health check endpoints.

---

9. Index and Constraint Summary

This table enumerates every uniqueness constraint and index across the schema.

---

Cross-References

• See Data Flow for the sequence in which these tables are populated during an update cycle, including the bucket-upsert pattern.
• See API Reference for the API endpoints that read from and write to these tables, including the GET /api/h1/campaign query shape and the rebroadcast endpoint.