Database Layer

Database architecture and implementation using Drizzle ORM

Database Layer

ShipKit uses PostgreSQL with Drizzle ORM for type-safe database operations. The database layer is designed for scalability, type safety, and maintainability.

Overview

The database system provides:

  • Type-safe schema definitions
  • Automated migrations
  • Connection pooling
  • Development tools
  • Activity logging
  • Multi-tenant support

Core Files

The database system is organized into several key files:

  1. src/server/db/index.ts - Database connection and configuration
  2. src/server/db/schema.ts - Schema definitions
  3. src/migrations/* - Database migrations

Database Connection

// src/server/db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const conn = globalForDb.conn ?? postgres(env.DATABASE_URL);
if (env.NODE_ENV !== "production") globalForDb.conn = conn;

export const db = drizzle(conn, { schema });

Connection Pooling

The connection is cached in development to avoid creating new connections on HMR updates:

const globalForDb = globalThis as unknown as {
  conn: postgres.Sql | undefined;
};

Schema Design

Core Tables

// src/server/db/schema.ts

// Users
export const users = createTable("user", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: varchar("name", { length: 255 }),
  email: varchar("email", { length: 255 }).notNull(),
  emailVerified: timestamp("email_verified", {
    mode: "date",
    withTimezone: true,
  }).default(sql`CURRENT_TIMESTAMP`),
  // ... other fields
});

// Teams
export const teams = createTable("team", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: varchar("name", { length: 255 }).notNull(),
  type: teamType("type").default("workspace").notNull(),
  // ... timestamps
});

// Projects
export const projects = createTable("project", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  name: varchar("name", { length: 255 }).notNull(),
  teamId: varchar("team_id", { length: 255 }).references(() => teams.id),
  // ... timestamps
});

Relations

// User Relations
export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts),
}));

// Team Relations
export const teamsRelations = relations(teams, ({ many }) => ({
  members: many(teamMembers),
}));

// Team Member Relations
export const teamMembersRelations = relations(teamMembers, ({ one }) => ({
  team: one(teams, { fields: [teamMembers.teamId], references: [teams.id] }),
  user: one(users, { fields: [teamMembers.userId], references: [users.id] }),
}));

Enums

export const teamType = pgEnum("team_type", ["personal", "workspace"]);
export const notificationChannelType = pgEnum("notification_channel_type", [
  "email",
  "sms",
  "push",
  "slack",
]);
export const notificationType = pgEnum("notification_type", [
  "security",
  "system",
  "marketing",
  "team",
]);

Type Safety

Table Types

// Inferred Types
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

export type Team = typeof teams.$inferSelect;
export type NewTeam = typeof teams.$inferInsert;

export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;

Query Types

All queries are fully typed, providing compile-time safety:

// Example typed query
const user = await db.query.users.findFirst({
  where: (users, { eq }) => eq(users.email, email),
  with: {
    accounts: true,
  },
});

Migrations

Migration Scripts

-- src/migrations/0000_lowly_sandman.sql
CREATE TYPE "public"."team_type" AS ENUM('personal', 'workspace');

CREATE TABLE "shipkit_user" (
  "id" varchar(255) PRIMARY KEY NOT NULL,
  "name" varchar(255),
  "email" varchar(255) NOT NULL,
  -- ... other fields
);

CREATE TABLE "shipkit_team" (
  "id" varchar(255) PRIMARY KEY NOT NULL,
  "name" varchar(255) NOT NULL,
  "type" "team_type" DEFAULT 'workspace' NOT NULL,
  -- ... timestamps
);

Migration Commands

# Generate migrations
pnpm db.generate

# Run migrations
pnpm db.migrate

# Push schema changes
pnpm db.push

# Reset database
pnpm db.reset

Activity Logging

The system includes comprehensive activity logging:

export const activityLog = createTable("activity_log", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  teamId: varchar("team_id", { length: 255 }),
  userId: varchar("user_id", { length: 255 }),
  action: varchar("action", { length: 255 }).notNull(),
  category: varchar("category", { length: 50 }).notNull(),
  severity: varchar("severity", { length: 20 }).default("info").notNull(),
  details: text("details"),
  metadata: text("metadata"),
  // ... other fields
});

Multi-tenancy

The system supports multi-tenancy through teams and projects:

// Team Members
export const teamMembers = createTable("team_member", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  userId: varchar("user_id", { length: 255 })
    .notNull()
    .references(() => users.id),
  teamId: varchar("team_id", { length: 255 })
    .notNull()
    .references(() => teams.id, { onDelete: "cascade" }),
  role: varchar("role", { length: 50 }).notNull(),
  // ... timestamps
});

// Project Members
export const projectMembers = createTable("project_member", {
  id: varchar("id", { length: 255 })
    .notNull()
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  projectId: varchar("project_id", { length: 255 })
    .notNull()
    .references(() => projects.id),
  userId: varchar("user_id", { length: 255 })
    .notNull()
    .references(() => users.id),
  role: varchar("role", { length: 50 }).notNull(),
  // ... timestamps
});

Development Tools

Drizzle Studio

Access the database GUI with:

pnpm db.studio

Features:

  • Schema visualization
  • Data browsing
  • Query builder
  • Relationship explorer

Performance Considerations

  1. Indexing

    • Automatic index creation on foreign keys
    • Custom indexes for frequently queried fields
    • Compound indexes for complex queries

  2. Connection Pooling

    • Development connection caching
    • Production connection pooling
    • Automatic cleanup

  3. Query Optimization

    • Type-safe query building
    • Relation preloading
    • Pagination support

Security Implementation

  1. Data Protection

    • SQL injection prevention through parameterized queries
    • Type-safe schema validation
    • Automatic input sanitization

  2. Access Control

    • Row-level security through team/project membership
    • Role-based access control
    • Audit logging

  3. Data Integrity

    • Foreign key constraints
    • Cascading deletes where appropriate
    • Timestamp tracking

Best Practices

  1. Schema Design

    • Use UUIDs for primary keys
    • Include audit timestamps
    • Implement soft deletes
    • Maintain referential integrity

  2. Query Patterns

    • Use transactions for multi-step operations
    • Implement proper error handling
    • Cache frequently accessed data
    • Use pagination for large datasets

  3. Development Workflow

    • Version control migrations
    • Test migrations before deployment
    • Maintain seed data
    • Document schema changes

Notes

  • All tables use the configured prefix (DB_PREFIX)
  • Timestamps use timezone-aware types
  • Soft deletes implemented where appropriate
  • Activity logging enabled by default
  • Type safety enforced throughout