Banking App (PostgreSQL with Drizzle ORM)

This document outlines the PostgreSQL database schema for a sample banking application. The schema is designed to be efficient, scalable, and easy to understand. We use Drizzle ORM for defining our data models with TypeScript.

The core of our application revolves around four main tables: User, Account, Transaction and Ledger.

Installation Guide

To add these schemas to your project, run:

npx servercn-cli add schema banking-app

If you install schema individually, the relationships between them won't be automatic—you'll need to implement them manually.

1. User Schema

The User schema stores essential information about the customer. This includes personal details and security credentials.

Path: src/drizzle/schemas/user.schema.ts

import {
  pgTable,
  serial,
  varchar,
  boolean,
  timestamp,
  integer,
  jsonb,
  uniqueIndex,
  index,
  pgEnum
} from "drizzle-orm/pg-core";
 
export const roleEnum = pgEnum("role", ["user", "admin"]);
export const providerEnum = pgEnum("provider", ["local", "google", "github"]);
 
export const timestamps = {
  createdAt: timestamp("created_at", { mode: "string" }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { mode: "string" })
    .defaultNow()
    .$onUpdate(() => new Date().toISOString())
    .notNull()
};
 
export interface IAvatar {
  public_id: string;
  url: string;
  size: number;
}
 
export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 100 }).notNull(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    password: varchar("password", { length: 255 }),
    role: roleEnum("role").default("user").notNull(),
 
    // OAuth Provider
    provider: providerEnum("provider").default("local").notNull(),
    providerId: varchar("provider_id", { length: 255 }),
 
    // Profile
    avatar: jsonb("avatar").$type<IAvatar>(),
 
    // Auth Metadata
    isEmailVerified: boolean("is_email_verified").default(false).notNull(),
    lastLoginAt: timestamp("last_login_at"),
    failedLoginAttempts: integer("failed_login_attempts").default(0).notNull(),
    lockUntil: timestamp("lock_until"),
 
    // Soft Delete
    isDeleted: boolean("is_deleted").default(false).notNull(),
    deletedAt: timestamp("deleted_at"),
    reActivateAvailableAt: timestamp("re_activate_available_at"),
 
    //? Timestamps
    ...timestamps
  },
  table => [
    uniqueIndex("email_idx").on(table.email),
    index("provider_idx").on(table.provider, table.providerId),
    index("role_idx").on(table.role),
    index("is_deleted_idx").on(table.isDeleted)
  ]
);
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Installation

npx servercn-cli add schema banking-app/user

2. Account Schema

The Account schema stores user bank accounts (e.g., savings, current). A user can own multiple accounts with different types, currencies, and statuses.

Path: src/drizzle/schemas/account.schema.ts

import {
  pgTable,
  boolean,
  index,
  serial,
  integer,
  pgEnum
} from "drizzle-orm/pg-core";
import { timestamps } from "./schema.helper";
import { users } from "./user.schema";
 
export const ACCOUNT_TYPES = ["savings", "current"] as const;
export const ACCOUNT_CURRENCIES = ["NPR", "INR", "USD"] as const;
export const ACCOUNT_STATUS = ["active", "frozen", "closed"] as const;
 
const currencyEnum = pgEnum("currency", ACCOUNT_CURRENCIES);
const accountTypeEnum = pgEnum("type", ACCOUNT_TYPES);
const statusEnum = pgEnum("status", ACCOUNT_STATUS);
 
export const accounts = pgTable(
  "accounts",
  {
    id: serial("id").primaryKey().notNull(),
    userId: integer("user_id")
      .notNull()
      .references(() => users.id),
    currency: currencyEnum("currency").notNull().default("NPR"),
    type: accountTypeEnum("type").notNull().default("savings"),
    status: statusEnum("status").default("active").notNull(),
    systemAccount: boolean("system_account").default(false).notNull(),
    ...timestamps
  },
  table => [
    index("user_id_idx").on(table.userId),
    index("status_idx").on(table.status),
    index("type_idx").on(table.type)
  ]
);
 
export type Account = typeof accounts.$inferSelect;
export type NewAccount = typeof accounts.$inferInsert;

Installation

npx servercn-cli add schema banking-app/account

3. Transaction Schema

The Transaction schema records money movements between accounts, including transfers, deposits, and withdrawals.

Path: src/drizzle/schemas/transaction.schema.ts

import {
  pgTable,
  varchar,
  integer,
  pgEnum,
  index,
  uniqueIndex,
  decimal,
  serial,
  integer
} from "drizzle-orm/pg-core";
import { timestamps } from "./schema.helper";
import { accounts } from "./account.schema";
 
// Transaction status
export const TRANSACTION_STATUS = [
  "pending",
  "completed",
  "failed",
  "reversed"
] as const;
 
const statusEnum = pgEnum("status", TRANSACTION_STATUS);
 
export const transactions = pgTable(
  "transactions",
  {
    id: serial("id").primaryKey().notNull(),
    fromAccountId: integer("from_account_id")
      .references(() => accounts.id)
      .notNull(),
    toAccountId: integer("to_account_id")
      .references(() => accounts.id)
      .notNull(),
    amount: decimal("amount", { precision: 15, scale: 2 }).notNull(),
    status: statusEnum("status").default("pending").notNull(),
    idempotencyKey: varchar("idempotency_key", { length: 255 })
      .notNull()
      .unique(),
    ...timestamps
  },
  table => [
    index("from_account_id_idx").on(table.fromAccountId),
    index("to_account_id_idx").on(table.toAccountId),
    index("status_idx").on(table.status),
    uniqueIndex("idempotency_key_idx").on(table.idempotencyKey)
  ]
);
 
export type Transaction = typeof transactions.$inferSelect;
export type NewTransaction = typeof transactions.$inferInsert;

Installation

npx servercn-cli add schema banking-app/transaction

4. Ledger Schema

The Ledger schema records all financial movements such as deposits, withdrawals, and transfers. Each entry represents a finalized financial event.

Path: src/drizzle/schemas/ledger.schema.ts

Ledger entries are immutable in business logic. Application layer should prevent updates/deletes.

import {
  pgTable,
  integer,
  pgEnum,
  index,
  decimal,
  serial
} from "drizzle-orm/pg-core";
import { timestamps } from "./schema.helper";
import { accounts } from "./account.schema";
import { transactions } from "./transaction.schema";
 
// Ledger entry types
export const LEDGER_ENTRY_TYPES = ["debit", "credit"] as const;
 
const entryTypeEnum = pgEnum("entry_type", LEDGER_ENTRY_TYPES);
 
export const ledgers = pgTable(
  "ledgers",
  {
    id: serial("id").primaryKey().notNull(),
    accountId: integer("account_id")
      .references(() => accounts.id)
      .notNull(),
    transactionId: integer("transaction_id")
      .references(() => transactions.id)
      .notNull(),
    entryType: entryTypeEnum("entry_type").notNull(),
    amount: decimal("amount", { precision: 15, scale: 2 }).notNull(),
    ...timestamps
  },
  table => [
    index("account_id_idx").on(table.accountId),
    index("transaction_id_idx").on(table.transactionId),
    index("entry_type_idx").on(table.entryType)
  ]
);
 
export type Ledger = typeof ledgers.$inferSelect;
export type NewLedger = typeof ledgers.$inferInsert;

Installation

npx servercn-cli add schema banking-app/ledger

5. Relations

Defines relationships between database tables for seamless data fetching with Drizzle ORM.

Path: src/drizzle/schemas/relations.ts

import { relations } from "drizzle-orm";
import { accounts } from "./account.schema";
import { transactions } from "./transaction.schema";
import { ledgers } from "./ledger.schema";
import { users } from "./user.schema";
 
// User relations
export const usersRelations = relations(users, ({ many }) => ({
  accounts: many(accounts)
}));
 
// Account relations
export const accountsRelations = relations(accounts, ({ one, many }) => ({
  user: one(users, {
    fields: [accounts.userId],
    references: [users.id]
  }),
  ledgers: many(ledgers)
}));
 
// Transaction relations
export const transactionsRelations = relations(transactions, ({ many }) => ({
  ledgers: many(ledgers)
}));
 
// Ledger relations
export const ledgersRelations = relations(ledgers, ({ one }) => ({
  account: one(accounts, {
    fields: [ledgers.accountId],
    references: [accounts.id]
  }),
  transaction: one(transactions, {
    fields: [ledgers.transactionId],
    references: [transactions.id]
  })
}));

6. Export all schemas

All database schemas must be re-exported from a single entry point. This allows Drizzle to load every table definition from one file during migration generation.

File Path: src/drizzle/index.ts

export * from "./schemas/user.schema";
export * from "./schemas/ledger.schema";
export * from "./schemas/transaction.schema";
export * from "./schemas/account.schema";

7. drizzle.config.ts

drizzle.config.ts 
import { Config, defineConfig } from "drizzle-kit";
 
export default defineConfig({
  out: "./src/drizzle/migrations",
  schema: "./src/drizzle/index.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  },
  verbose: true,
  strict: true
}) satisfies Config;

File & Folder Structure

ServerCN

Select a file to view its contents

Installation

npx servercn-cli add schema banking-app