Database Schema

Hackorda's data layer is PostgreSQL accessed through Drizzle ORM. The schema is the source of truth for a crowd-QA / bug-bounty platform: organizations run test cycles against their products, testers file issues, admins triage and settle payouts, and a small set of platform tables (API keys, notifications, audit, rate-limiting) keep it safe to operate.

A legacy LMS / quiz / hackathon-event data model also lives in the same database. It is off the current product path — present, migrated, but not where the active QA work happens. It's documented at the end for completeness.

How the schema is organized

The schema is split by domain under src/db/schema/<domain>.ts, each file owning its tables plus the enum constants those tables reference. Two barrels tie it together:

• src/db/schema/index.ts — re-exports every domain file in dependency order (foundational tables first, leaf domains last).
• src/db/schema.ts — a 12-line barrel that does nothing but export * from "./schema/index". It exists so drizzle.config.ts (schema: './src/db/schema.ts') and the ~108 callers that import … from "@/db/schema" never had to change when the single file was split into a folder. There is no monolithic schema file anymore — to add a table, drop it in the right domain file and re-export it from schema/index.ts.

Domain files:

The role model

Roles are a normalized reference table, not a Postgres enum.

• users.roleId is an integer FK → roles.id (roles lives in _enums.ts). roles is a seeded lookup table (id, name, description).

• The valid values come from the ROLES const in _enums.ts:

  export const ROLES = {
    ADMIN: 1,
    STUDENT: 2,
    GUEST: 3,
    QA: 4,
    SUPER_ADMIN: 5,
  } as const;

  STUDENT (2) is the effective default for a normal signup; SUPER_ADMIN (5) is a tier above ADMIN (added in migration 0036_super_admin_role.sql) — super-admins are recognized as admins everywhere, plus they own the money/role-sensitive actions.

There is no user_role Postgres enum. Older versions of this doc described a CREATE TYPE "user_role" AS ENUM('student','admin') — that is wrong and was never in the current schema. The very first migration (0000_medical_iron_fist.sql) creates a roles table with a users.role_id FK; there is not a single CREATE TYPE statement in the migration history.

roleId is a global role. Per-context roles are separate and do not elevate global access:

• Per-cycle: testers.role uses the TESTER_ROLES const — tester | lead | observer.
• Per-team (legacy events): userTeams.role uses TEAM_ROLES — owner | member.

The core QA domain

Catalog — who is testing what

The spine is a strict ownership chain: organizations → products → productVersions → testCycles, with testers joined in per cycle.

testCycles.kind (TEST_CYCLE_KINDS: standard | verification | feature) drives the tester view and which issues populate the cycle's queue. verification cycles re-test fixed bugs pulled from sourceCycleIds; feature cycles scope to featureLabels.

Execution — sessions and what testers find

Test cases — structured (vs. exploratory) testing

A reusable, product-scoped test-case library that issues can reference: testCases + testSuites/testSuiteCases (the library), cycleTestCases (a cycle's test plan, with a content snapshot so the plan is stable even if the source case changes), and testExecutions (append-only run history). An issue can link back to the testCaseId / testExecutionId that surfaced it.

Payouts — settling accepted bugs

Payout state on an issue itself lives in issues.payoutStatus / payoutAmountCents / payoutCurrency (the decision); issuePayouts records the money moving.

AI — agent provenance

Platform — operating the system

Key enums

All enums are const objects $type-tagged onto varchar columns (or integer FKs to a reference table for the legacy _enums.ts set). Values below are the real ones from the schema.

For exact column lists, types, and the onDelete behaviour of every FK, read the domain file directly — the schema is the source of truth and this table deliberately doesn't duplicate it.

Migrations

Migrations are generated with drizzle-kit (dialect: 'postgresql', schema: './src/db/schema.ts', out: './src/db/migrations') and live in src/db/migrations/ with the journal under migrations/meta/.

There are 41 migrations, numbered 0000_* through 0040_last_roulette.sql (the journal in meta/_journal.json has 41 entries). The history is real and substantial — a few markers along the way:

• 0000_medical_iron_fist.sql — initial schema, including the roles table.
• 0028_verification_gate.sql — verification gate on payouts.
• 0029_payout_batches.sql — batched settlement runs.
• 0031_scale_indexes.sql — compound indexes for the hot list paths.
• 0032_audit_log.sql / 0033_request_idempotency.sql — platform safety tables.
• 0034_test_cases.sql — the structured test-case domain.
• 0036_super_admin_role.sql — the 5th role.
• 0040_last_roulette.sql — latest.

Connection, SSL & pooling

src/db/index.ts builds a pg.Pool and wraps it with drizzle(pool) (drizzle-orm/node-postgres). Highlights:

• Pool tuning: max (DB_POOL_MAX, default 20), idleTimeoutMillis 30s, connectionTimeoutMillis 10s, and a per-session statement_timeout (DB_STATEMENT_TIMEOUT_MS, default 15s) set on every new connection.
• SSL is mode-driven for managed Postgres: DB_CA_CERT (verify against a CA bundle), DB_SSL_MODE (verify/require/no-verify/allow/disable), and a fail-closed default (SSL on, rejectUnauthorized: true) in production when nothing else is set. Local/non-production with nothing set runs without SSL.

For provisioning, backups, the managed-provider cutover, and SSL operational detail, see ops/database.md.