Database Schema

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: ['./src/db/schema.ts', './src/db/auth-schema.ts'],
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

-- Owner type
CREATE TYPE owner_type AS ENUM ('user', 'organization');

-- PR state
CREATE TYPE pr_state AS ENUM ('open', 'closed', 'merged');

-- Issue state
CREATE TYPE issue_state AS ENUM ('open', 'closed');

-- Issue status (Kanban)
CREATE TYPE issue_status AS ENUM (
  'triage', 'backlog', 'todo', 'in_progress',
  'in_review', 'done', 'canceled'
);

-- Issue priority
CREATE TYPE issue_priority AS ENUM (
  'none', 'low', 'medium', 'high', 'urgent'
);

-- Issue relation type
CREATE TYPE issue_relation_type AS ENUM (
  'blocks', 'blocked_by', 'relates_to',
  'duplicates', 'duplicated_by'
);

-- Project status
CREATE TYPE project_status AS ENUM (
  'backlog', 'planned', 'in_progress',
  'paused', 'completed', 'canceled'
);

-- Project health
CREATE TYPE project_health AS ENUM (
  'on_track', 'at_risk', 'off_track'
);

-- Milestone state
CREATE TYPE milestone_state AS ENUM ('open', 'closed');

-- Review state
CREATE TYPE review_state AS ENUM (
  'pending', 'approved', 'changes_requested', 'commented'
);

-- Permission level
CREATE TYPE permission AS ENUM ('read', 'write', 'admin');

-- Organization role
CREATE TYPE org_role AS ENUM ('member', 'admin', 'owner');

-- Workflow run state
CREATE TYPE workflow_run_state AS ENUM (
  'queued', 'in_progress', 'completed', 'failed', 'cancelled'
);

-- Merge queue state
CREATE TYPE merge_queue_state AS ENUM (
  'pending', 'preparing', 'testing', 'ready',
  'merging', 'completed', 'failed', 'cancelled'
);

-- Merge queue strategy
CREATE TYPE merge_queue_strategy AS ENUM (
  'sequential', 'optimistic', 'adaptive'
);

-- Review request state
CREATE TYPE review_request_state AS ENUM (
  'pending', 'completed', 'dismissed'
);

-- Package visibility
CREATE TYPE package_visibility AS ENUM ('public', 'private');

# Generate migration from schema changes
npx drizzle-kit generate

# This creates a new file in src/db/migrations/
# e.g., 0018_new_feature.sql

# Apply all pending migrations
npx drizzle-kit migrate

# Or push schema directly (development only)
npx drizzle-kit push

-- Create a down migration manually
-- 0018_rollback_new_feature.sql

ALTER TABLE my_table DROP COLUMN new_column;

// Get PR with author
const result = await db
  .select()
  .from(pullRequests)
  .innerJoin(user, eq(pullRequests.authorId, user.id))
  .where(eq(pullRequests.id, prId));

const conditions = [eq(issues.repoId, repoId)];

if (state) {
  conditions.push(eq(issues.state, state));
}

if (assigneeId) {
  conditions.push(eq(issues.assigneeId, assigneeId));
}

const result = await db
  .select()
  .from(issues)
  .where(and(...conditions));

await db
  .update(repositories)
  .set({
    starsCount: sql`GREATEST(0, ${repositories.starsCount} + ${delta})`,
  })
  .where(eq(repositories.id, repoId));

const result = await db
  .select()
  .from(issues)
  .where(eq(issues.repoId, repoId))
  .orderBy(desc(issues.createdAt))
  .limit(limit)
  .offset(offset);