Database Schema

Complete PostgreSQL schema reference for ShipFree using Drizzle ORM

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

id · text · required

Primary key. User identifier.

name · text · required

User’s display name

email · text · required

Unique email address

emailVerified · boolean · default: false

Email verification status

image · text

Profile image URL (nullable)

createdAt · timestamp · default: "now()"

Account creation timestamp

updatedAt · 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

id · text · required

Primary key. Session identifier.

expiresAt · timestamp · required

Session expiration time

token · text · required

Unique session token (stored in cookies)

createdAt · timestamp · default: "now()"

Session creation timestamp

updatedAt · timestamp

Last activity timestamp

ipAddress · text

Client IP address (nullable)

userAgent · text

Client user agent string (nullable)

userId · 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

id · text · required

Primary key. Account identifier.

accountId · text · required

Provider’s account identifier

providerId · text · required

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

userId · text · required

Foreign key to user.id (cascades on delete)

accessToken · text

OAuth access token (nullable, encrypted recommended)

refreshToken · text

OAuth refresh token (nullable, encrypted recommended)

idToken · text

OAuth ID token (nullable)

accessTokenExpiresAt · timestamp

Access token expiration (nullable)

refreshTokenExpiresAt · timestamp

Refresh token expiration (nullable)

scope · text

OAuth scopes granted (nullable)

password · text

Hashed password for email/password auth (nullable)

createdAt · timestamp · default: "now()"

Account link timestamp

updatedAt · 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

id · text · required

Primary key. Verification identifier.

identifier · text · required

Email address or user identifier

value · text · required

Verification token or OTP code

expiresAt · timestamp · required

Token/OTP expiration time

createdAt · timestamp · default: "now()"

Creation timestamp

updatedAt · timestamp

Update timestamp

Indexes:

• Index on identifier

Payment Tables

customer

Stores payment provider customer records.

Location: src/database/schema.ts:77-97

id · text · required

Primary key. Internal customer identifier.

userId · text · required

Foreign key to user.id (cascades on delete)

provider · text · required

Payment provider: stripe, polar, or lemonsqueezy

providerCustomerId · text · required

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

email · text

Customer email at provider (nullable)

createdAt · timestamp · default: "now()"

Record creation timestamp

updatedAt · 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

id · text · required

Primary key. Internal subscription identifier.

userId · text · required

Foreign key to user.id (cascades on delete)

customerId · text

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

provider · text · required

Payment provider: stripe, polar, or lemonsqueezy

providerSubscriptionId · text · required

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

status · text · required

Subscription status:

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

plan · text · required

Plan name: free, starter, pro, or enterprise

interval · text

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

amount · decimal(10,2)

Subscription price amount (nullable)

currency · text

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

currentPeriodStart · timestamp

Current billing period start (nullable)

currentPeriodEnd · timestamp

Current billing period end (nullable)

cancelAtPeriodEnd · boolean · default: false

Whether subscription cancels at period end

canceledAt · timestamp

Cancellation timestamp (nullable)

trialStart · timestamp

Trial period start (nullable)

trialEnd · timestamp

Trial period end (nullable)

createdAt · timestamp · default: "now()"

Subscription creation timestamp

updatedAt · 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

id · text · required

Primary key. Internal payment identifier.

userId · text · required

Foreign key to user.id (cascades on delete)

customerId · text

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

subscriptionId · text

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

provider · text · required

Payment provider: stripe, polar, or lemonsqueezy

providerPaymentId · text · required

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

type · text · required

Payment type:

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

status · text · required

Payment status:

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

amount · decimal(10,2) · required

Payment amount

currency · text · required

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

description · text

Payment description (nullable)

createdAt · timestamp · default: "now()"

Payment timestamp

updatedAt · 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

id · text · required

Primary key. Purchase identifier.

stripeSessionId · text · required

Unique Stripe Checkout session ID

stripeCustomerEmail · text

Customer email from Stripe (nullable)

githubEmail · text

GitHub email for repo access (nullable)

githubUsername · text

GitHub username for repo access (nullable)

twitterHandle · text

Twitter handle for attribution (nullable)

amountPaid · decimal(10,2)

Purchase amount (nullable)

currency · text

Currency code (nullable)

createdAt · timestamp · default: "now()"

Purchase timestamp

updatedAt · 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