@izri/database

Database package with Drizzle ORM and schema definitions

Overview

The database package provides schema definitions, inferred types, and a configured Drizzle client for PostgreSQL. It is the single source of truth for application data structures used across tRPC routers and services.

Package Structure

packages/database/
├── src/
│   ├── index.ts          # Database client export
│   ├── schema.ts         # Schema definitions (Drizzle)
│   └── seed.ts           # Seed data (optional)
├── migrations/           # SQL migrations
├── drizzle.config.ts     # Drizzle configuration
└── package.json

Schema (summary)

Key tables and constraints defined in src/schema.ts:

• Users (better-auth compatible)

  • Table: user
  • Columns: id, email, name, image, emailVerified, createdAt, updatedAt
  • Uniques: users_email_key

• Organizations

  • Table: organizations
  • Columns: id, name, slug, description, createdAt, updatedAt
  • Uniques: organizations_slug_key

• Organization Members

  • Table: organization_members
  • Columns: id, organizationId, userId, role, createdAt
  • Uniques: organization_members_organization_id_user_id_key
  • Indexes: organization_members_org_user_idx

• Projects

  • Table: projects
  • Columns: id, name, description, repository, branch, language, framework, userId, organizationId, createdAt, updatedAt
  • Indexes: projects_org_idx, projects_user_idx

• Project Analyses

  • Table: project_analyses
  • Columns: id, projectId, repoUrl, commitSha, branch, analysis (JSONB), createdAt
  • Uniques: project_analyses_project_id_commit_sha_key
  • Indexes: project_analyses_project_created_idx

• Project Settings

  • Table: project_settings
  • Columns: id, projectId, enableUnitTests, enableIntegrationTests, enableE2ETests, autoRunTests, notifyOnFailure, aiProvider, aiModel, createdAt, updatedAt
  • Uniques: project_settings_project_id_key

• Test Runs

  • Table: test_runs
  • Columns: id, projectId, userId, type, status, duration, coverage, results (JSONB), logs, error, startedAt, completedAt, createdAt, passedTests, failedTests, skippedTests, totalTests
  • Indexes: test_runs_project_idx, test_runs_user_idx

• Sessions (better-auth)

  • Table: session
  • Columns: id, token, userId, ipAddress, userAgent, expiresAt, createdAt, updatedAt
  • Uniques: sessions_token_key
  • Indexes: sessions_user_id_idx

• Accounts (better-auth OAuth)

  • Table: account
  • Columns: id, providerId, accountId, password, accessToken, refreshToken, idToken, scope, accessTokenExpiresAt, refreshTokenExpiresAt, userId, createdAt, updatedAt
  • Uniques: accounts_provider_account_key
  • Indexes: accounts_user_id_idx

• Verifications (better-auth)

  • Table: verification
  • Columns: id, identifier, value, expiresAt, createdAt, updatedAt

• API Tokens

  • Table: api_tokens
  • Columns: id, userId, name, token (hashed), scopes (text[]), lastUsedAt, expiresAt, createdAt
  • Uniques: api_tokens_token_key
  • Indexes: api_tokens_user_idx

• Auth Audit Logs

  • Table: auth_audit_logs
  • Columns: id, userId, action, ipAddress, userAgent, metadata (JSONB), createdAt
  • Indexes: auth_audit_logs_user_idx, auth_audit_logs_action_idx

ID strategy

• All IDs use ULID (Universally Unique Lexicographically Sortable Identifier)
• Auth tables (user/session/account/verification) use ULID via Better Auth's advanced.generateId configuration
• Application tables (organizations, organization_members, projects, project_settings, project_analyses, test_runs, api_tokens, auth_audit_logs) use ULID generated at the application layer
• All IDs are stored as varchar(191) for cross-DB compatibility and indexing characteristics
• ULIDs are 26 characters long and provide natural time-based sorting

Why ULID?

• Lexicographically sortable (IDs sort by creation time)
• URL-safe (no special characters)
• Case-insensitive (lowercase for consistency)
• 128-bit compatibility with UUID
• Better database index performance than random UUIDs

Example (application usage):

import { ulid } from 'ulid'
import { db, projects } from '@izri/database'

const projectId = ulid()
await db.insert(projects).values({
  id: projectId,
  name: 'My Project',
  repository: 'https://github.com/user/repo',
  branch: 'main',
  language: 'typescript',
  organizationId: 'org-id',
  userId: 'user-id',
})

Important: Always use ulid() from the ulid package for generating new IDs. Never use crypto.randomUUID() or other ID generation methods.

Usage

import { db } from '@izri/database'
import { projects } from '@izri/database/src/schema'

const allProjects = await db.select().from(projects)

Commands

# Generate migration
pnpm db:generate

# Apply migrations
pnpm db:migrate

# Seed database
pnpm db:seed

# Open Drizzle Studio
pnpm db:studio

Related Documentation

• ../../development/database-management.md — Full database guide
• ../../backend/database-queries.md — Query patterns