# @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):

```ts
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

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

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

## Commands

```bash
# 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