Database Development

Jubiloop uses PostgreSQL through AdonisJS Lucid. This page covers schema migrations, models, relationships, repository queries, and database-change safety. Local service startup and connection details belong in Local Development Setup.

Schema Sources

Use these sources together rather than treating a prose table inventory as authoritative:

• apps/server/database/migrations/ defines schema history and constraints.
• apps/server/app/models/ defines Lucid columns, hooks, and relationships.
• Data Models Overview links to domain-specific schema pages.

Current core relationships include users joining organizations through members, organizations owning events, and members joining events through event_collaborators. Better Auth credential hashes live in accounts.password; runtime session tokens use Redis.

Migrations

Run migration commands from apps/server:

node ace make:migration create_table_name
node ace make:migration add_column_to_table_name --table=table_name
node ace migration:run
node ace migration:status
node ace migration:run --dry-run
node ace migration:rollback

Migration filenames use timestamped snake_case names. Define foreign keys, delete behavior, indexes, nullability, and timezone-aware timestamps explicitly. This shortened example follows 1751799644739_create_organizations_table.ts:

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'organizations'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.uuid('id').primary().defaultTo(this.db.rawQuery('gen_random_uuid()').knexQuery)
      table.uuid('owner_id').notNullable().references('id').inTable('users').onDelete('RESTRICT')
      table.string('name').notNullable()
      table.string('slug').notNullable().unique()
      table.timestamp('created_at', { useTz: true }).notNullable()

      table.index(['owner_id'])
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}

Migration safety

Never run node ace migration:rollback --batch=0. It drops every table managed by migrations. Prefer a forward repair migration for shared environments. Use migration:fresh only when an approved local reset may delete all local data.

Before a risky production change, confirm a usable recovery point. Dev and QA don't have scheduled database backups. Follow the environment-specific rollback and reset runbook, not an ad hoc dump command copied from local development.

Models

Application models with UUID primary keys extend BaseModelWithUuid. It assigns UUID v7 values in a beforeCreate hook; migration defaults provide a database fallback. Use Luxon DateTime for date and timestamp fields.

This shortened example follows apps/server/app/models/event.ts:

import { belongsTo, column, hasMany } from '@adonisjs/lucid/orm'
import type { BelongsTo, HasMany } from '@adonisjs/lucid/types/relations'
import { DateTime } from 'luxon'
import BaseModelWithUuid from './base_model_with_uuid.js'
import EventPlan from './event_plan.js'
import Organization from './organization.js'

export default class Event extends BaseModelWithUuid {
  @column()
  declare organizationId: string

  @column()
  declare name: string

  @column.dateTime({ autoCreate: true })
  declare createdAt: DateTime

  @column.dateTime({ autoCreate: true, autoUpdate: true })
  declare updatedAt: DateTime

  @belongsTo(() => Organization)
  declare organization: BelongsTo<typeof Organization>

  @hasMany(() => EventPlan)
  declare plans: HasMany<typeof EventPlan>
}

Define model-specific enums beside the model. Reserve hooks for invariants that must hold for every write path, and return early from async hooks when the relevant fields aren't dirty.

Relationships And Queries

Declare both sides needed by the application and configure non-standard pivot or polymorphic keys explicitly. Event.members, for example, uses event_collaborators with event_id and member_id.

Repositories own direct Lucid access and map expected database failures to neverthrow results. Scope tenant-owned records in the query instead of fetching by ID and checking the organization afterward. EventRepository.findByIdAndOrganization uses this pattern:

const event = await Event.query()
  .where('id', eventId)
  .where('organization_id', organizationId)
  .first()

Preload relationships when the caller needs them to avoid N+1 queries:

const event = await Event.query().where('id', eventId).preload('plans').first()

await event?.load('organization')

Add indexes for actual lookup and ordering patterns, especially foreign keys used for tenant scope. Keep validation at the appropriate boundary: VineJS for request input, model hooks for invariants, and database constraints for persisted integrity.

Database Maintenance

• Use the local infrastructure command reference for psql, Redis, and Docker access. It owns the current container names.
• Use Local Development Troubleshooting for service logs, connection failures, and destructive local resets.
• Use Deployment Infrastructure and Environments for backup and recovery policy.