Database Schema - ShipFree

ShipFree uses PostgreSQL with Drizzle ORM for type-safe database operations. The schema is defined in src/database/schema.ts.

Database Connection

From src/database/index.ts:1-16:

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'
import { env } from '@/config/env'

const queryClient = postgres(env.DATABASE_URL)

export const db = drizzle({ client: queryClient, schema })

Authentication Tables

user

Stores user account information. Location: src/database/schema.ts:4-15

text

required

Primary key. User identifier.

text

required

User’s display name

text

required

Unique email address

boolean

default:false

Email verification status

text

Profile image URL (nullable)

timestamp

default:"now()"

Account creation timestamp

timestamp

default:"now()"

Last update timestamp (auto-updated)

Indexes:

Unique index on email

Relations:

sessions → Many session records
accounts → Many account records
customers → Many customer records
subscriptions → Many subscription records
payments → Many payment records

session

Stores active user sessions managed by Better-Auth. Location: src/database/schema.ts:17-34

text

required

Primary key. Session identifier.

timestamp

required

Session expiration time

text

required

Unique session token (stored in cookies)

timestamp

default:"now()"

Session creation timestamp

timestamp

Last activity timestamp

text

Client IP address (nullable)

text

Client user agent string (nullable)

text

required

Foreign key to user.id (cascades on delete)

Indexes:

Index on userId
Unique index on token

Relations:

user → One user record

account

Stores OAuth provider accounts linked to users. Location: src/database/schema.ts:36-58

text

required

Primary key. Account identifier.

text

required

Provider’s account identifier

text

required

OAuth provider name (e.g., ‘google’, ‘github’)

text

required

Foreign key to user.id (cascades on delete)

text

OAuth access token (nullable, encrypted recommended)

text

OAuth refresh token (nullable, encrypted recommended)

text

OAuth ID token (nullable)

timestamp

Access token expiration (nullable)

timestamp

Refresh token expiration (nullable)

text

OAuth scopes granted (nullable)

text

Hashed password for email/password auth (nullable)

timestamp

default:"now()"

Account link timestamp

timestamp

Last update timestamp

Indexes:

Index on userId

Relations:

user → One user record

verification

Stores email verification tokens and OTP codes. Location: src/database/schema.ts:60-74

text

required

Primary key. Verification identifier.

text

required

Email address or user identifier

text

required

Verification token or OTP code

timestamp

required

Token/OTP expiration time

timestamp

default:"now()"

Creation timestamp

timestamp

Update timestamp

Indexes:

Index on identifier

Payment Tables

customer

Stores payment provider customer records. Location: src/database/schema.ts:77-97

text

required

Primary key. Internal customer identifier.

text

required

Foreign key to user.id (cascades on delete)

text

required

Payment provider: stripe, polar, or lemonsqueezy

text

required

Customer ID from payment provider (e.g., cus_xxx for Stripe)

text

Customer email at provider (nullable)

timestamp

default:"now()"

Record creation timestamp

timestamp

Last update timestamp

Indexes:

Index on userId
Index on providerCustomerId

Relations:

user → One user record
subscriptions → Many subscription records
payments → Many payment records

subscription

Stores subscription records across all payment providers. Location: src/database/schema.ts:99-132

text

required

Primary key. Internal subscription identifier.

text

required

Foreign key to user.id (cascades on delete)

text

Foreign key to customer.id (sets null on delete)

text

required

Payment provider: stripe, polar, or lemonsqueezy

text

required

Subscription ID from payment provider (e.g., sub_xxx for Stripe)

text

required

Subscription status:

active - Active subscription
canceled - Canceled
past_due - Payment failed
trialing - In trial period
incomplete - Incomplete payment
paused - Paused subscription

text

required

Plan name: free, starter, pro, or enterprise

text

Billing interval: month, year, or null for one-time

decimal(10,2)

Subscription price amount (nullable)

text

Currency code (e.g., usd, eur)

timestamp

Current billing period start (nullable)

timestamp

Current billing period end (nullable)

boolean

default:false

Whether subscription cancels at period end

timestamp

Cancellation timestamp (nullable)

timestamp

Trial period start (nullable)

timestamp

Trial period end (nullable)

timestamp

default:"now()"

Subscription creation timestamp

timestamp

Last update timestamp

Indexes:

Index on userId
Index on customerId
Index on providerSubscriptionId
Index on status

Relations:

user → One user record
customer → One customer record
payments → Many payment records

payment

Stores individual payment transactions. Location: src/database/schema.ts:134-164

text

required

Primary key. Internal payment identifier.

text

required

Foreign key to user.id (cascades on delete)

text

Foreign key to customer.id (sets null on delete)

text

Foreign key to subscription.id (sets null on delete)

text

required

Payment provider: stripe, polar, or lemonsqueezy

text

required

Payment ID from provider (e.g., pi_xxx for Stripe)

text

required

Payment type:

subscription - Recurring subscription payment
one_time - One-time purchase
refund - Refund transaction

text

required

Payment status:

succeeded - Payment successful
pending - Payment processing
failed - Payment failed
canceled - Payment canceled
refunded - Payment refunded

decimal(10,2)

required

Payment amount

text

required

Currency code (e.g., usd, eur)

text

Payment description (nullable)

timestamp

default:"now()"

Payment timestamp

timestamp

Last update timestamp

Indexes:

Index on userId
Index on customerId
Index on subscriptionId
Index on providerPaymentId

Relations:

user → One user record
customer → One customer record
subscription → One subscription record

premiumPurchase

Stores premium one-time purchases (Stripe Checkout sessions). Location: src/database/schema.ts:166-184

text

required

Primary key. Purchase identifier.

text

required

Unique Stripe Checkout session ID

text

Customer email from Stripe (nullable)

text

GitHub email for repo access (nullable)

text

GitHub username for repo access (nullable)

text

Twitter handle for attribution (nullable)

decimal(10,2)

Purchase amount (nullable)

text

Currency code (nullable)

timestamp

default:"now()"

Purchase timestamp

timestamp

Last update timestamp

Indexes:

Unique index on stripeSessionId

Relations Overview

From src/database/schema.ts:186-240:

// User relations
export const userRelations = relations(user, ({ many }) => ({
  sessions: many(session),
  accounts: many(account)
}))

// Session relations
export const sessionRelations = relations(session, ({ one }) => ({
  user: one(user, { fields: [session.userId], references: [user.id] })
}))

// Account relations
export const accountRelations = relations(account, ({ one }) => ({
  user: one(user, { fields: [account.userId], references: [user.id] })
}))

// Customer relations
export const customerRelations = relations(customer, ({ one, many }) => ({
  user: one(user, { fields: [customer.userId], references: [user.id] }),
  subscriptions: many(subscription),
  payments: many(payment)
}))

// Subscription relations
export const subscriptionRelations = relations(subscription, ({ one, many }) => ({
  user: one(user, { fields: [subscription.userId], references: [user.id] }),
  customer: one(customer, { fields: [subscription.customerId], references: [customer.id] }),
  payments: many(payment)
}))

// Payment relations
export const paymentRelations = relations(payment, ({ one }) => ({
  user: one(user, { fields: [payment.userId], references: [user.id] }),
  customer: one(customer, { fields: [payment.customerId], references: [customer.id] }),
  subscription: one(subscription, { fields: [payment.subscriptionId], references: [subscription.id] })
}))

Migrations

Generate and run migrations using Drizzle Kit:

# Generate migration from schema changes
bun run generate-migration

# Run migrations locally
bun run migrate:local

# Run migrations in production
bun run migrate

Migrations are stored in the migrations/ directory.

Type Safety

Drizzle provides full TypeScript types for all tables:

import { db } from '@/database'
import { user, subscription } from '@/database/schema'
import { eq } from 'drizzle-orm'

// Type-safe queries
const users = await db.select().from(user)
const activeSubscriptions = await db
  .select()
  .from(subscription)
  .where(eq(subscription.status, 'active'))

Next Steps

Query Examples

Common database query patterns

Authentication

Using auth-related tables

Payments

Working with payment tables

​Database Connection

​Authentication Tables

​user

​session

​account

​verification

​Payment Tables

​customer

​subscription

​payment

​premiumPurchase

​Relations Overview

​Migrations

​Type Safety

​Next Steps

Query Examples

Authentication

Payments

Database Connection

Authentication Tables

user

session

account

verification

Payment Tables

customer

subscription

payment

premiumPurchase

Relations Overview

Migrations

Type Safety

Next Steps