Build a Wholesale Receivables Lab

Model wholesale sales and payments in PostgreSQL with Drizzle.

Introduction

30 Second Summary

Wholesale customers often buy stock before settling the full bill. A missing payment history leaves the shop guessing about the balance still owed.

In this project, you will build an isolated accounts receivable lab using PostgreSQL with Drizzle ORM. The finished script proves that 20000.00 sold minus 13000.00 received leaves 7000.00 outstanding as PARTIALLY_PAID.

What You'll Build

You will run the finished lab to watch separate partial-payment records roll into a trustworthy balance summary.

By the end of this project, you'll have:

  • A repeatable wholesale demo that creates a local-shop customer plus sale through isolated database migrations.
  • A payment ledger that preserves every amount plus its method and receipt time in a separate row.
  • A receivables report that separates sold revenue from cash received. It also calculates the outstanding balance plus payment state.
  • Secret Mission: Find balances that remain unpaid after their due date.

Are there any prerequisites?

You need an existing Node.js project backed by a development PostgreSQL database that can safely hold temporary lab tables.

The project assumes Visual Studio Code plus npm already work with Drizzle on Windows.

Before We Start

This checkpoint fixes the lab's scope before any database work begins. Your isolated receivables lab must leave production and Better Auth tables untouched while showing what a shop bought, what it paid, and what it still owes.

Isolate and Verify the Database Lab

An unconfirmed PostgreSQL schema can contain working application tables or Better Auth data. Testing new accounts receivable structures there could disrupt the application you already have.

An isolated lab gives you a safe place to test the database connection. You will protect the current project before proving that the lab reaches only your development database.

In this step, get ready to:
  • Protect the current project with a Git branch or local backup.
  • Install the pinned lab dependencies.
  • Prove that the development database responds to the connection script.
Protect the project and install dependencies

A Git branch or local backup gives you a safe return point before the lab changes dependencies. Exact package versions keep the lab aligned with the code used throughout this project.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open Visual Studio Code.
  • Use the folder picker in the top menu to select the existing Node.js project folder that contains package.json.
  • Open a new integrated terminal from the top menu.
  • Select PowerShell as the terminal profile if another shell is active.

Use a Git branch

A branch keeps every lab change separate from the current project history. The new branch starts from the project state you have open.

  • Create the lab branch from the integrated PowerShell terminal by running this command:
git switch -c wholesale-receivables-lab

What does this command do?

The command creates the wholesale-receivables-lab branch from your current project state. It also switches the open project to that branch.

  • Confirm that the branch indicator in Visual Studio Code now shows wholesale-receivables-lab.

Could not create the branch?

Confirm that the integrated terminal belongs to the existing project repository. An existing branch with the same name can also block creation.

Help me create a safe Git branch for this database lab.

Create a local backup

A copied project folder provides a local recovery point when the project does not use Git. Keep the copy beside the original so it is easy to identify.

  • Press the Windows key to open Windows search.
  • Type File Explorer into the search field.
  • Press Enter to open File Explorer.
  • Select the folder that contains your existing Node.js project.
  • Copy the selected project folder.
  • Paste the copy beside the original folder.
  • Rename the copied folder by appending -wholesale-lab-backup to its existing name.
  • Confirm that the renamed backup appears beside the original project folder.

The lab uses Drizzle ORM version 0.45.3 for its schema code. Drizzle Kit version 0.31.11 manages its isolated migrations.

The database connection comes from node-postgres version 8.23.0. The remaining packages load configuration or run TypeScript files.

  • Return to the integrated PowerShell terminal in the original project folder.
  • Install all pinned lab packages by running these commands:
npm install drizzle-orm@0.45.3 pg@8.23.0 dotenv@18.0.2
npm install --save-dev drizzle-kit@0.31.11 tsx@4.23.15 @types/pg@8.23.1

What do these packages provide?

  • The drizzle-orm package provides the schema APIs used by the lab.
  • The pg package provides the PostgreSQL connection pool.
  • The dotenv package loads values from .env.
  • The drizzle-kit package manages generated database migrations.
  • The tsx package runs TypeScript scripts from PowerShell.
  • The @types/pg package supplies TypeScript declarations for the PostgreSQL client.
  • Confirm that both installation commands return to the PowerShell prompt without an installation error.
  • Open package.json in Visual Studio Code.
  • Confirm that drizzle-orm, pg, and dotenv appear as runtime packages.
  • Confirm that drizzle-kit, tsx, and @types/pg appear as development packages.

Did a package fail to install?

Confirm that PowerShell is open inside the folder containing package.json. A network interruption can also stop package downloads.

Help me diagnose the pinned npm dependency installation.

Build the guarded development connection probe

An environment variable keeps the database target outside the TypeScript source file. The probe expects DATABASE_URL to point to the development PostgreSQL database.

Keep the database URL private

A database URL can contain credentials. The existing .env stays uncommitted throughout this lab.

Screenshots must hide the URL value. The final connection message proves access without exposing the credential.

  • Open the existing .env file from the project folder in Visual Studio Code.
  • Locate the line that begins with DATABASE_URL.
  • Compare its connection target with the development PostgreSQL database intended for this lab.
  • Stop the lab if the value identifies a production database.
  • Confirm that the project's version control rules exclude .env.

A matching development target establishes the lab's safety boundary. The remaining check proves that the target accepts a query.

The connection probe asks PostgreSQL for its current server time. A finally block closes the connection pool after success or failure.

  • Create the src/wholesale-lab folder inside the existing project folder with the Visual Studio Code file tree.
  • Create connection.ts inside src/wholesale-lab.
  • Paste the connection probe into src/wholesale-lab/connection.ts using the code below:
import "dotenv/config";
import { Pool } from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  const result = await pool.query<{ server_time: Date }>(
    "select now() as server_time",
  );
  console.log("PostgreSQL connection successful", result.rows[0].server_time);
} finally {
  await pool.end();
}

What does this code do?

  • The configuration import loads the existing .env file before the script reads its values.
  • The DATABASE_URL guard stops the script when the connection value is missing.
  • The Pool instance manages the PostgreSQL connection.
  • The pool.query call requests the server timestamp under server_time.
  • The pool.end call closes the pool even when the query fails.
  • Save src/wholesale-lab/connection.ts.
  • Confirm that connection.ts appears inside src/wholesale-lab in the Visual Studio Code file tree.

Does the connection file show an import problem?

Confirm that the pinned packages were installed in the same project folder as connection.ts. Saving the file also gives TypeScript a chance to refresh its package information.

Help me diagnose the TypeScript imports in my connection probe.

✔️ Awesome, I've got everything!

Your saved connection probe contains the environment guard, PostgreSQL query, success message, and pool cleanup.

ⓧ I'd like to double check the full code

import "dotenv/config";
import { Pool } from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  const result = await pool.query<{ server_time: Date }>(
    "select now() as server_time",
  );
  console.log("PostgreSQL connection successful", result.rows[0].server_time);
} finally {
  await pool.end();
}
Run the development connection check

A successful query proves that the isolated script can reach the selected development server. It also confirms that the connection pool shuts down cleanly after the response.

Before you run the final check, do you expect the development database to return its current server time?

  • Run the connection probe from the project folder with this PowerShell command:
npx tsx src/wholesale-lab/connection.ts

What should you see?

The terminal prints PostgreSQL connection successful followed by a server timestamp. PowerShell then returns to its prompt.

That timestamp proves that the verified development target accepted the query. The returned prompt proves that pool.end closed the connection pool.

Did the connection check fail?

If you see DATABASE_URL is required, confirm that .env is in the existing project folder. Confirm that PowerShell is running the command from that same folder.

Help me diagnose the development PostgreSQL connection without exposing DATABASE_URL.

That is the safety boundary working. Your isolated lab can now reach its development database without modifying any application tables.

Your protected connection path is ready. Next, you will use it to record the first shop sale.

Record the First Shop Sale

Your successful connection check proved that the lab can reach the safe development PostgreSQL database. The next useful proof is a real shop sale.

An isolated Drizzle Kit configuration gives this experiment its own schema path. It also gives the lab a separate migration history.

In this step, get ready to:
  • Configure an isolated schema path and migration folder.
  • Define the temporary customer and sale tables.
  • Migrate the lab tables and display the first wholesale sale.
Point Drizzle Kit at the lab

The configuration tells Drizzle Kit which schema file to read. Its separate output path keeps these experimental migrations away from the application's normal migration folder.

  • Select the Explorer view in Visual Studio Code.
  • Select the folder that contains package.json.
  • Select the New File... button.
  • Enter drizzle.wholesale.config.ts as the file name.
  • Configure the isolated migration path by pasting this code:
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/wholesale-lab/schema.ts",
  out: "./drizzle-wholesale",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
  migrations: {
    table: "__drizzle_wholesale_lab_migrations",
    schema: "drizzle",
  },
});

What does this configuration control?

  • The schema value points Drizzle Kit at the isolated wholesale schema.
  • The out value sends generated files to drizzle-wholesale.
  • The dbCredentials.url value reuses the development database connection you verified earlier.
  • The migration settings record this lab's history in drizzle.__drizzle_wholesale_lab_migrations.
  • Save drizzle.wholesale.config.ts.
  • Confirm the Explorer view lists drizzle.wholesale.config.ts beside package.json.

Configuration file in the wrong place?

Confirm that drizzle.wholesale.config.ts sits in the same folder as package.json. A nested location changes how the relative schema path resolves.

Help me check the location and paths in my isolated Drizzle configuration.

✔️ Awesome, I've got everything!

Your isolated Drizzle configuration now points at the wholesale lab. The application's normal schema path remains unchanged.

ⓧ I'd like to double check the full code

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/wholesale-lab/schema.ts",
  out: "./drizzle-wholesale",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
  migrations: {
    table: "__drizzle_wholesale_lab_migrations",
    schema: "drizzle",
  },
});
Define the temporary customer and sale tables

A customer-to-sale relationship gives each wholesale purchase a named shop. A foreign key connects the sale to that shop.

This first Drizzle ORM model keeps one temporary payment method on the sale. That simple shape produces a working sale before you test its limits.

  • Select the src/wholesale-lab folder in the Explorer view.
  • Select the New File... button.
  • Enter schema.ts as the file name.
  • Define the first customer and sale model by pasting this code:
import {
  numeric,
  pgEnum,
  snakeCase,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

// Constrain the business values used by the temporary sale model.
export const wholesaleLabSalesChannel = pgEnum(
  "wholesale_lab_sales_channel",
  ["RETAIL", "WHOLESALE"],
);
export const wholesaleLabPaymentMethod = pgEnum(
  "wholesale_lab_payment_method",
  ["CASH", "CARD", "EASYPAISA"],
);

// Give each wholesale sale a named shop.
export const wholesaleLabCustomers = snakeCase.table("wholesale_lab_customers", {
  id: uuid().primaryKey().defaultRandom(),
  name: varchar({ length: 200 }).notNull(),
});

// Keep one temporary payment method directly on each sale.
export const wholesaleLabSales = snakeCase.table("wholesale_lab_sales", {
  id: uuid().primaryKey().defaultRandom(),
  customerId: uuid().references(() => wholesaleLabCustomers.id),
  salesChannel: wholesaleLabSalesChannel().default("RETAIL").notNull(),
  totalAmount: numeric({ precision: 10, scale: 2 }).notNull(),
  paymentMethod: wholesaleLabPaymentMethod().notNull(),
});

How does the first model work?

  • The exported enum definitions constrain the channel and payment method values.
  • The wholesaleLabCustomers table gives each shop a generated identifier.
  • The wholesaleLabSales table links a purchase to its customer.
  • The numeric column preserves the sale total as an exact decimal value.
  • The temporary paymentMethod column stores one method per sale.
  • Save src/wholesale-lab/schema.ts.
  • Inspect schema.ts for red TypeScript error underlines.
  • Confirm the file shows no TypeScript errors.

Seeing TypeScript errors in the schema?

Check that every imported name matches its use in the file. Confirm that both table definitions are exported.

Help me diagnose the TypeScript errors in my temporary Drizzle customer and sale schema.

✔️ Awesome, I've got everything!

The temporary schema now describes a named customer plus one wholesale sale. Its single payment field is ready for the first demonstration.

ⓧ I'd like to double check the full code

import {
  numeric,
  pgEnum,
  snakeCase,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

// Constrain the business values used by the temporary sale model.
export const wholesaleLabSalesChannel = pgEnum(
  "wholesale_lab_sales_channel",
  ["RETAIL", "WHOLESALE"],
);
export const wholesaleLabPaymentMethod = pgEnum(
  "wholesale_lab_payment_method",
  ["CASH", "CARD", "EASYPAISA"],
);

// Give each wholesale sale a named shop.
export const wholesaleLabCustomers = snakeCase.table("wholesale_lab_customers", {
  id: uuid().primaryKey().defaultRandom(),
  name: varchar({ length: 200 }).notNull(),
});

// Keep one temporary payment method directly on each sale.
export const wholesaleLabSales = snakeCase.table("wholesale_lab_sales", {
  id: uuid().primaryKey().defaultRandom(),
  customerId: uuid().references(() => wholesaleLabCustomers.id),
  salesChannel: wholesaleLabSalesChannel().default("RETAIL").notNull(),
  totalAmount: numeric({ precision: 10, scale: 2 }).notNull(),
  paymentMethod: wholesaleLabPaymentMethod().notNull(),
});
Generate the migration and record a sale

Drizzle Kit compares the exported schema with the database state. It writes the resulting SQL into the lab's separate migration folder.

  • Switch back to the PowerShell terminal from the connection check.
  • Generate the initial wholesale migration by running:
npx drizzle-kit generate --config=drizzle.wholesale.config.ts --name=wholesale_lab_base

What does this command generate?

Drizzle Kit reads src/wholesale-lab/schema.ts through the isolated configuration. It writes a timestamped migration folder under drizzle-wholesale.

The generated folder contains migration.sql for review. It also contains the schema snapshot used for later differences.

  • Expand drizzle-wholesale in the Explorer view.
  • Expand the timestamped migration folder.
  • Open migration.sql.
  • Confirm the migration creates only the wholesale lab enum types and lab tables.
  • Confirm the migration does not alter any application table.

Migration files missing or unexpected?

Check that the terminal command used drizzle.wholesale.config.ts. Confirm that the config points to src/wholesale-lab/schema.ts.

Help me inspect an unexpected Drizzle migration before I apply it.

Applying the migration changes the safe development database you verified earlier. The isolated configuration keeps the lab's migration record separate.

  • Apply the reviewed wholesale migration by running:
npx drizzle-kit migrate --config=drizzle.wholesale.config.ts

What does this command apply?

Drizzle Kit applies the unapplied files from drizzle-wholesale to the verified development database. It records this lab's migration in drizzle.__drizzle_wholesale_lab_migrations.

  • Confirm the terminal reports that the migration completed.
  • Confirm the PowerShell prompt becomes available again.

Migration did not complete?

Stop if the database connection points outside development. Check the generated SQL before retrying any failed migration.

Help me diagnose why my isolated wholesale Drizzle migration did not complete.

The tables now need a small transaction that proves the relationship works. The temporary script creates one shop before recording its first sale.

  • Select the src/wholesale-lab folder in the Explorer view.
  • Select the New File... button.
  • Enter naive-demo.ts as the file name.
  • Create the temporary sale demonstration by pasting this code:
import "dotenv/config";
import { eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { wholesaleLabCustomers, wholesaleLabSales } from "./schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool });

try {
  const rows = await db.transaction(async (tx) => {
    // Create a distinct shop for this demonstration run.
    const [customer] = await tx.insert(wholesaleLabCustomers).values({ name: `Demo Corner Shop ${Date.now()}` }).returning({ id: wholesaleLabCustomers.id });

    // Record one wholesale sale with one temporary payment method.
    const [sale] = await tx.insert(wholesaleLabSales).values({ customerId: customer.id, salesChannel: "WHOLESALE", totalAmount: "20000.00", paymentMethod: "CASH" }).returning({ id: wholesaleLabSales.id });

    // Read the linked shop and sale back from PostgreSQL.
    return tx.select({ shop: wholesaleLabCustomers.name, saleTotal: wholesaleLabSales.totalAmount, paymentMethod: wholesaleLabSales.paymentMethod }).from(wholesaleLabSales).innerJoin(wholesaleLabCustomers, eq(wholesaleLabSales.customerId, wholesaleLabCustomers.id)).where(eq(wholesaleLabSales.id, sale.id));
  });

  console.table(rows);
} finally {
  await pool.end();
}

What does the temporary script prove?

  • The transaction inserts a uniquely named shop into wholesale_lab_customers.
  • The sale links that shop to a WHOLESALE purchase worth 20000.00.
  • The query joins the two records before printing the result.
  • The temporary model reports one payment method from the sale row.
  • Save src/wholesale-lab/naive-demo.ts.

Before you run the final check, which shop details do you expect the joined result to display?

  • Run the temporary sale demonstration with this command:
npx tsx src/wholesale-lab/naive-demo.ts

What should you see?

The terminal displays a table containing a shop name that starts with Demo Corner Shop. The same row shows 20000.00 as the sale total.

The row also shows one CASH payment method. That visible sale is the baseline for testing partial payments.

That is the first workflow proven. Your isolated database can now connect a named shop to a wholesale sale.

Sale table missing or empty?

Confirm that the migration completed before running the script. Check that naive-demo.ts imports both exported tables from ./schema.

Help me diagnose why the temporary wholesale sale script does not display its shop and sale.

✔️ Awesome, I've got everything!

Your temporary script inserts a named shop and displays its first wholesale sale. The result includes one payment method for the model you will challenge next.

ⓧ I'd like to double check the full code

import "dotenv/config";
import { eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { wholesaleLabCustomers, wholesaleLabSales } from "./schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool });

try {
  const rows = await db.transaction(async (tx) => {
    // Create a distinct shop for this demonstration run.
    const [customer] = await tx.insert(wholesaleLabCustomers).values({ name: `Demo Corner Shop ${Date.now()}` }).returning({ id: wholesaleLabCustomers.id });

    // Record one wholesale sale with one temporary payment method.
    const [sale] = await tx.insert(wholesaleLabSales).values({ customerId: customer.id, salesChannel: "WHOLESALE", totalAmount: "20000.00", paymentMethod: "CASH" }).returning({ id: wholesaleLabSales.id });

    // Read the linked shop and sale back from PostgreSQL.
    return tx.select({ shop: wholesaleLabCustomers.name, saleTotal: wholesaleLabSales.totalAmount, paymentMethod: wholesaleLabSales.paymentMethod }).from(wholesaleLabSales).innerJoin(wholesaleLabCustomers, eq(wholesaleLabSales.customerId, wholesaleLabCustomers.id)).where(eq(wholesaleLabSales.id, sale.id));
  });

  console.table(rows);
} finally {
  await pool.end();
}

Your first wholesale sale is stored behind an isolated migration path. Next, you will make its single payment field lose information on purpose.

Make the Single-Payment Model Fail

Your isolated PostgreSQL lab now records a named shop with a wholesale sale totaling 20000.00.

This step pressure-tests the temporary paymentMethod field with two partial payments. The result must reveal whether one sale column can retain each receipt event.

In this step, get ready to:
  • Represent two partial payments against the same sale.
  • Inspect the sale after the later payment overwrites its method.
  • Record why the remaining data cannot produce an outstanding balance.
Represent two partial payments

Two receipts against the same sale create a realistic payment history. Applying them in time order exposes what the current model retains.

  • Switch back to src/wholesale-lab/naive-demo.ts in the Explorer sidebar of Visual Studio Code.
  • Locate the temporary logic that stores one payment method on the 20000.00 WHOLESALE sale.
  • Replace the one-event example with a sequence where an 8000 payment uses CASH before a later 5000 payment overwrites paymentMethod with CARD.
  • Keep the existing final sale print after the later update.
  • Save src/wholesale-lab/naive-demo.ts.
Run the failure test

The final database row is the evidence for what the current shape remembers. This run tests whether that row can still describe both receipts.

Before you run the demo, do you expect the final sale row to preserve both payment events?

  • Run the updated temporary demo in the PowerShell terminal from earlier:
npx tsx src/wholesale-lab/naive-demo.ts

What does the output prove?

  • The final paymentMethod value is CARD.
  • The sale row no longer retains CASH as the earlier method.
  • The row preserves neither the 8000 amount nor the later 5000 amount as payment events.
  • Without those amounts, the model cannot calculate the 7000.00 outstanding balance.

You've exposed the model's breaking point. The sale survives while its partial-payment history disappears.

Still seeing CASH in the final row?

Confirm that src/wholesale-lab/naive-demo.ts is saved. Check that the later update to CARD runs before the final sale print.

If the output still does not reflect the later update, ask for help tracing the update sequence.

✔️ Awesome, I've got everything!

Your temporary demo now proves that one payment field loses receipt history. You have the evidence needed to redesign the model.

ⓧ I'd like to double check the full code

This test leaves the isolated configuration from earlier unchanged. Compare those supporting files with the references below.

  • Compare drizzle.wholesale.config.ts with this reference:
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/wholesale-lab/schema.ts",
  out: "./drizzle-wholesale",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
  migrations: {
    table: "__drizzle_wholesale_lab_migrations",
    schema: "drizzle",
  },
});

Why this file remains unchanged

  • The schema path remains ./src/wholesale-lab/schema.ts.
  • Generated migrations remain isolated in ./drizzle-wholesale.
  • Migration history remains in drizzle.__drizzle_wholesale_lab_migrations.
  • Compare src/wholesale-lab/connection.ts with this reference:
import "dotenv/config";
import { Pool } from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  const result = await pool.query<{ server_time: Date }>(
    "select now() as server_time",
  );
  console.log("PostgreSQL connection successful", result.rows[0].server_time);
} finally {
  await pool.end();
}

Why this file remains available

  • The file still reads DATABASE_URL from the existing environment.
  • The query still confirms the database connection with a server timestamp.
  • Confirm that src/wholesale-lab/naive-demo.ts first represents 8000 through CASH.
  • Confirm that the later 5000 event overwrites paymentMethod with CARD.
  • Confirm that the final output retains only the latest payment method.
  • Confirm that the existing customer table remains in place.
  • Confirm that the existing sale table remains in place.
  • Confirm that the initial isolated migration remains in place.

You now have clear evidence that the current shape loses receipt history. Next, you'll introduce a payment ledger that preserves every receipt.

Replace the Payment Field with a Ledger

Your last run proved that one payment field loses the history behind a wholesale sale. The database still knows the latest method while the earlier receipt disappears.

A payment ledger gives every receipt its own row. This step uses an isolated Drizzle ORM migration to add that ledger to your development PostgreSQL database.

In this step, get ready to:
  • Replace the single payment field with a child payment table.
  • Generate an isolated migration named wholesale_payment_ledger.
  • Create one sale with two preserved payment records.
Replace the temporary schema

The final schema separates the sale from the money received against it. Each row in wholesale_lab_payments points back to one sale through saleId.

  • Press the Windows key to open Windows Search.
  • Type Visual Studio Code into Windows Search.
  • Press Enter to open Visual Studio Code.
  • Select src/wholesale-lab/schema.ts in the Explorer sidebar.
  • Press Ctrl+A in the editor.
  • Press Delete to clear the temporary schema.
  • Add the schema imports plus the two enum definitions by pasting this first chunk:
import { sql } from "drizzle-orm";
import {
  check,
  index,
  numeric,
  pgEnum,
  snakeCase,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const wholesaleLabSalesChannel = pgEnum(
  "wholesale_lab_sales_channel",
  ["RETAIL", "WHOLESALE"],
);

export const wholesaleLabPaymentMethod = pgEnum(
  "wholesale_lab_payment_method",
  ["CASH", "CARD", "EASYPAISA"],
);

What does this schema foundation define?

  • The imports provide the PostgreSQL column builders used by the three lab tables.
  • The wholesaleLabSalesChannel enum separates retail sales from wholesale sales.
  • The wholesaleLabPaymentMethod enum limits receipt methods to the supported values.
  • Save src/wholesale-lab/schema.ts.
  • Check the Problems panel for unresolved import errors.

Seeing unresolved imports?

Confirm that every import matches the first chunk. A missing sql or pgEnum import prevents the schema definitions from compiling.

Check the pinned dependencies from Step 1 if the package imports remain unresolved.

Help me diagnose unresolved imports in my Drizzle wholesale schema.

  • Add the customer table below the enum definitions by pasting this chunk:
export const wholesaleLabCustomers = snakeCase.table(
  "wholesale_lab_customers",
  {
    id: uuid().primaryKey().defaultRandom(),
    name: varchar({ length: 200 }).notNull(),
    phone: varchar({ length: 30 }),
    address: text(),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_customers_name_not_blank",
      sql`length(trim(${table.name})) > 0`,
    ),
  ],
);

What does the customer table preserve?

  • Each shop receives a generated identifier through id.
  • The contact columns keep the shop identity separate from its sales.
  • The name check prevents a blank customer name from entering the lab.
  • Save src/wholesale-lab/schema.ts.
  • Confirm that wholesaleLabCustomers has no red underline in the editor.

Is the customer table underlined?

Check the closing brackets around the table callback. Confirm that the check import remains in the first chunk.

Help me fix the customer table in my wholesale schema.

  • Add the sale table below wholesaleLabCustomers by pasting this chunk:
export const wholesaleLabSales = snakeCase.table(
  "wholesale_lab_sales",
  {
    id: uuid().primaryKey().defaultRandom(),
    customerId: uuid().references(() => wholesaleLabCustomers.id, {
      onDelete: "restrict",
    }),
    salesChannel: wholesaleLabSalesChannel().default("RETAIL").notNull(),
    totalAmount: numeric({ precision: 10, scale: 2 }).notNull(),
    dueAt: timestamp({ withTimezone: true }),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_sales_total_positive",
      sql`${table.totalAmount} > 0`,
    ),
    check(
      "wholesale_lab_sales_customer_required",
      sql`${table.salesChannel} <> 'WHOLESALE' OR ${table.customerId} IS NOT NULL`,
    ),
    index("wholesale_lab_sales_customer_idx").on(table.customerId),
    index("wholesale_lab_sales_due_at_idx").on(table.dueAt),
  ],
);

How does a sale connect to a shop?

  • The customerId foreign key connects a sale to its customer.
  • Restrictive deletion protects a customer while a related financial record exists.
  • The totalAmount column stores the exact sale value as a PostgreSQL numeric.
  • Save src/wholesale-lab/schema.ts.
  • Confirm that the editor resolves wholesaleLabCustomers.id inside the sale table.

Is the customer reference unresolved?

Confirm that wholesaleLabCustomers appears above wholesaleLabSales. Check the spelling of the referenced id column.

Help me fix the customer foreign key in wholesaleLabSales.

  • Add the payment ledger below wholesaleLabSales by pasting this chunk:
export const wholesaleLabPayments = snakeCase.table(
  "wholesale_lab_payments",
  {
    id: uuid().primaryKey().defaultRandom(),
    saleId: uuid()
      .notNull()
      .references(() => wholesaleLabSales.id, { onDelete: "restrict" }),
    amount: numeric({ precision: 10, scale: 2 }).notNull(),
    method: wholesaleLabPaymentMethod().notNull(),
    receivedAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
    reference: text(),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_payments_amount_positive",
      sql`${table.amount} > 0`,
    ),
    index("wholesale_lab_payments_sale_idx").on(table.saleId),
    index("wholesale_lab_payments_received_at_idx").on(table.receivedAt),
  ],
);

Why does the ledger fix the data loss?

  • Every payment receives its own amount through amount.
  • Every payment preserves its method through method.
  • The receivedAt value records when the receipt entered the ledger.
  • The saleId foreign key lets one sale own many payment rows.
  • Save src/wholesale-lab/schema.ts.
  • Confirm that the Problems panel shows no TypeScript errors for the schema.

Is the payment table still failing?

Confirm that wholesaleLabSales appears before wholesaleLabPayments. Check that wholesaleLabPaymentMethod matches the enum name near the top of the file.

Help me diagnose the wholesale payment table definition.

Generate and apply the ledger migration

A migration turns the schema change into reviewable SQL. The isolated configuration keeps this migration inside drizzle-wholesale.

  • Return to the PowerShell session from the previous step.
  • Generate the named ledger migration by running this command:
npx drizzle-kit generate --config=drizzle.wholesale.config.ts --name=wholesale_payment_ledger

What does generation create?

Drizzle Kit compares the exported schema with the previous migration snapshot. It writes the new SQL into a timestamped folder under drizzle-wholesale.

The generated migration.sql gives you a review point before PostgreSQL changes.

  • Expand drizzle-wholesale in the Visual Studio Code Explorer sidebar.
  • Expand the newest timestamped migration folder.
  • Select migration.sql.
  • Confirm that the SQL creates the wholesale payment table.
  • Confirm that the SQL links each payment to the sale table.
  • Confirm that the old single payment field is removed from the sale table.

Why inspect generated SQL?

The SQL preview shows the exact database changes before they run. This check keeps an unexpected table change away from the development database.

  • Return to the same PowerShell session.
  • Apply the inspected migration by running this command:
npx drizzle-kit migrate --config=drizzle.wholesale.config.ts

What does migration apply?

Drizzle Kit applies the unapplied SQL through the isolated wholesale configuration. It records the migration in __drizzle_wholesale_lab_migrations.

The development database now has a child ledger table for wholesale receipts. The original one-field design has been replaced.

Did migration generation or application fail?

Confirm that PowerShell is still inside the existing Node.js project. Check that DATABASE_URL still points to the safe development database.

Confirm that drizzle.wholesale.config.ts still references src/wholesale-lab/schema.ts.

Help me diagnose the isolated wholesale migration.

Create the multi-payment demonstration

The schema can now preserve separate receipt events. The new demonstration inserts one wholesale sale inside a transaction before adding the two payment rows.

  • Select src/wholesale-lab/naive-demo.ts in the Visual Studio Code Explorer sidebar.
  • Press Delete.
  • Select Move to Recycle Bin if Visual Studio Code asks for confirmation.
  • Select the src/wholesale-lab folder in the Explorer sidebar.
  • Click the New File button at the top of the Explorer sidebar.
  • Enter demo.ts as the file name.
  • Press Enter to create the file.
  • Add the database imports plus the connection setup by pasting this chunk:
import "dotenv/config";
import { eq, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import {
  wholesaleLabCustomers,
  wholesaleLabPayments,
  wholesaleLabSales,
} from "./schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool });

How does the script reach the ledger?

  • The Pool uses the existing development database connection string.
  • The db client lets Drizzle execute typed inserts and queries.
  • The three schema imports expose the customer table, sale table, and payment table.
  • Add the transaction opening plus the customer and sale inserts below the db declaration:
try {
  const saleId = await db.transaction(async (tx) => {
    const [customer] = await tx
      .insert(wholesaleLabCustomers)
      .values({
        name: `Demo Corner Shop ${Date.now()}`,
        phone: "0300-0000000",
      })
      .returning({ id: wholesaleLabCustomers.id });

    const [sale] = await tx
      .insert(wholesaleLabSales)
      .values({
        customerId: customer.id,
        salesChannel: "WHOLESALE",
        totalAmount: "20000.00",
        dueAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000),
      })
      .returning({ id: wholesaleLabSales.id });

What does the transaction establish?

  • The transaction groups the related inserts into one database operation.
  • The returned customer identifier connects the shop to its wholesale sale.
  • The returned sale identifier becomes the parent key for each payment.
  • Add the two payment rows directly below the sale insert:
    await tx.insert(wholesaleLabPayments).values([
      {
        saleId: sale.id,
        amount: "8000.00",
        method: "CASH",
        reference: "First partial payment",
      },
      {
        saleId: sale.id,
        amount: "5000.00",
        method: "CARD",
        reference: "Second partial payment",
      },
    ]);

    return sale.id;
  });

How does this preserve both receipts?

  • The first row preserves an 8000.00 receipt with the CASH method.
  • The second row preserves a 5000.00 receipt with the CARD method.
  • Each row receives its own receivedAt timestamp from the table default.
  • The returned saleId identifies the sale outside the transaction.
  • Add the payment query plus connection cleanup below the transaction:
  const paymentRows = await db
    .select({
      amount: wholesaleLabPayments.amount,
      method: wholesaleLabPayments.method,
      receivedAt: wholesaleLabPayments.receivedAt,
    })
    .from(wholesaleLabPayments)
    .where(eq(wholesaleLabPayments.saleId, saleId));

  console.table(paymentRows);
} finally {
  await pool.end();
}

What does the final query prove?

  • The query filters payment rows through the returned saleId.
  • The selected columns show each amount, method, and receipt time.
  • The finally block closes the database pool after the demonstration.
  • Save src/wholesale-lab/demo.ts.
  • Confirm that the Problems panel shows no TypeScript errors for the demonstration.

Seeing errors in demo.ts?

Check that all three schema exports appear in the import from ./schema. Confirm that every opening bracket in the transaction has a matching closing bracket.

Help me fix the wholesale ledger demonstration.

Before you run the final check, do you expect one overwritten method or two separate payment rows?

  • Run the ledger demonstration in the same PowerShell session with this command:
npx tsx src/wholesale-lab/demo.ts

What should you see?

The terminal prints two payment rows. One row shows 8000.00 with CASH.

The other row shows 5000.00 with CARD. Both rows include a receipt timestamp.

That is the ledger working. The second receipt now extends the sale history instead of erasing the first receipt.

Do you see fewer than two rows?

Confirm that the insert passes two objects to wholesaleLabPayments. Check that both objects use the same sale.id value.

Confirm that the final query filters with the returned saleId. A different identifier can hide the newly inserted rows.

Help me diagnose why my wholesale demo does not print both payments.

✔️ Awesome, I've got everything!

Your ledger preserves both partial payments against the same sale. Keep the terminal output ready for the receivables calculation.

ⓧ I'd like to double check the full code

Compare your schema with this complete version.

import { sql } from "drizzle-orm";
import {
  check,
  index,
  numeric,
  pgEnum,
  snakeCase,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const wholesaleLabSalesChannel = pgEnum(
  "wholesale_lab_sales_channel",
  ["RETAIL", "WHOLESALE"],
);

export const wholesaleLabPaymentMethod = pgEnum(
  "wholesale_lab_payment_method",
  ["CASH", "CARD", "EASYPAISA"],
);

export const wholesaleLabCustomers = snakeCase.table(
  "wholesale_lab_customers",
  {
    id: uuid().primaryKey().defaultRandom(),
    name: varchar({ length: 200 }).notNull(),
    phone: varchar({ length: 30 }),
    address: text(),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_customers_name_not_blank",
      sql`length(trim(${table.name})) > 0`,
    ),
  ],
);

export const wholesaleLabSales = snakeCase.table(
  "wholesale_lab_sales",
  {
    id: uuid().primaryKey().defaultRandom(),
    customerId: uuid().references(() => wholesaleLabCustomers.id, {
      onDelete: "restrict",
    }),
    salesChannel: wholesaleLabSalesChannel().default("RETAIL").notNull(),
    totalAmount: numeric({ precision: 10, scale: 2 }).notNull(),
    dueAt: timestamp({ withTimezone: true }),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_sales_total_positive",
      sql`${table.totalAmount} > 0`,
    ),
    check(
      "wholesale_lab_sales_customer_required",
      sql`${table.salesChannel} <> 'WHOLESALE' OR ${table.customerId} IS NOT NULL`,
    ),
    index("wholesale_lab_sales_customer_idx").on(table.customerId),
    index("wholesale_lab_sales_due_at_idx").on(table.dueAt),
  ],
);

export const wholesaleLabPayments = snakeCase.table(
  "wholesale_lab_payments",
  {
    id: uuid().primaryKey().defaultRandom(),
    saleId: uuid()
      .notNull()
      .references(() => wholesaleLabSales.id, { onDelete: "restrict" }),
    amount: numeric({ precision: 10, scale: 2 }).notNull(),
    method: wholesaleLabPaymentMethod().notNull(),
    receivedAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
    reference: text(),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_payments_amount_positive",
      sql`${table.amount} > 0`,
    ),
    index("wholesale_lab_payments_sale_idx").on(table.saleId),
    index("wholesale_lab_payments_received_at_idx").on(table.receivedAt),
  ],
);

Compare your demonstration with this complete version for the current step.

import "dotenv/config";
import { eq, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import {
  wholesaleLabCustomers,
  wholesaleLabPayments,
  wholesaleLabSales,
} from "./schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool });

try {
  const saleId = await db.transaction(async (tx) => {
    const [customer] = await tx
      .insert(wholesaleLabCustomers)
      .values({
        name: `Demo Corner Shop ${Date.now()}`,
        phone: "0300-0000000",
      })
      .returning({ id: wholesaleLabCustomers.id });

    const [sale] = await tx
      .insert(wholesaleLabSales)
      .values({
        customerId: customer.id,
        salesChannel: "WHOLESALE",
        totalAmount: "20000.00",
        dueAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000),
      })
      .returning({ id: wholesaleLabSales.id });

    await tx.insert(wholesaleLabPayments).values([
      {
        saleId: sale.id,
        amount: "8000.00",
        method: "CASH",
        reference: "First partial payment",
      },
      {
        saleId: sale.id,
        amount: "5000.00",
        method: "CARD",
        reference: "Second partial payment",
      },
    ]);

    return sale.id;
  });

  const paymentRows = await db
    .select({
      amount: wholesaleLabPayments.amount,
      method: wholesaleLabPayments.method,
      receivedAt: wholesaleLabPayments.receivedAt,
    })
    .from(wholesaleLabPayments)
    .where(eq(wholesaleLabPayments.saleId, saleId));

  console.table(paymentRows);
} finally {
  await pool.end();
}

Your sale now keeps every receipt as a separate ledger event. Next, you will calculate how much was sold, received, and left outstanding.

Calculate What Was Sold, Received, and Owed

Your PostgreSQL payment ledger now preserves both receipts against one wholesale sale. The next goal is to turn those rows into a dependable accounts receivable summary.

Storing payments alone does not tell the business how much it sold or how much remains collectible. This step protects the financial rows before deriving those answers from the ledger.

In this step, get ready to:
  • Enforce the final customer and amount rules.
  • Aggregate every payment recorded against a sale.
  • Report the sold amount and received amount with the outstanding balance.
Protect the financial rows

A check constraint rejects an invalid value before it reaches a table. An index gives PostgreSQL a faster path to rows used by customer and payment queries.

The existing restrictive foreign keys continue to protect dependent financial records. The new row-level rules cover positive amounts and required wholesale customers.

  • Return to schema.ts in the Visual Studio Code window from the previous step.
  • Replace the existing Drizzle import groups with this expanded import group:
import { sql } from "drizzle-orm";
import {
  check,
  index,
  numeric,
  pgEnum,
  snakeCase,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

What do these imports enable?

  • The sql helper expresses each database rule using the table's columns.
  • The check helper attaches those rules to the generated table definitions.
  • The index helper declares lookup paths that Drizzle Kit can include in a migration.
  • Add the customer-name rule after the columns inside wholesaleLabCustomers by using this callback:
  (table) => [
    check(
      "wholesale_lab_customers_name_not_blank",
      sql`length(trim(${table.name})) > 0`,
    ),
  ],
);

Why validate the customer name?

The rule trims surrounding whitespace before checking the name length. A customer row therefore needs a visible shop name.

  • Add the amount rule and wholesale-customer rule after the columns inside wholesaleLabSales by using this callback:
  (table) => [
    check(
      "wholesale_lab_sales_total_positive",
      sql`${table.totalAmount} > 0`,
    ),
    check(
      "wholesale_lab_sales_customer_required",
      sql`${table.salesChannel} <> 'WHOLESALE' OR ${table.customerId} IS NOT NULL`,
    ),
    index("wholesale_lab_sales_customer_idx").on(table.customerId),
    index("wholesale_lab_sales_due_at_idx").on(table.dueAt),
  ],
);

How are sales protected?

  • The positive-total rule prevents zero-value or negative sales.
  • The customer rule requires every WHOLESALE sale to identify the shop that owes the balance.
  • The customer and due-date indexes support receivables lookups without storing another editable status.
  • Add the payment rule after the columns inside wholesaleLabPayments by using this callback:
  (table) => [
    check(
      "wholesale_lab_payments_amount_positive",
      sql`${table.amount} > 0`,
    ),
    index("wholesale_lab_payments_sale_idx").on(table.saleId),
    index("wholesale_lab_payments_received_at_idx").on(table.receivedAt),
  ],
);

How are payments protected?

Every receipt must have a positive amount. The two indexes support payment lookup by sale and receipt time.

  • Save src/wholesale-lab/schema.ts.
  • Ask Drizzle Kit to detect the schema changes by running this command in the PowerShell terminal from earlier:
npx drizzle-kit generate --config=drizzle.wholesale.config.ts

What does generation produce?

Drizzle Kit compares the exported schema with its latest migration snapshot. Detected changes produce a new timestamped folder inside drizzle-wholesale.

That folder contains migration.sql for the database changes. The SQL remains isolated from the application's normal migration path.

  • Open the newest timestamped folder under drizzle-wholesale in the Visual Studio Code Explorer sidebar.
  • Select migration.sql inside that folder.

You should see SQL for the new checks and indexes. The migration should preserve the three existing wholesale lab tables.

  • Apply the inspected migration to the safe development database by running this command:
npx drizzle-kit migrate --config=drizzle.wholesale.config.ts

What does migration confirm?

Drizzle Kit applies unapplied SQL from the isolated migration folder. It records the result in drizzle.__drizzle_wholesale_lab_migrations.

Did the schema migration fail?

Confirm that PowerShell still uses the development database connection from the earlier safety check. Inspect the generated SQL for a constraint or index that already exists before retrying.

Help me diagnose the isolated wholesale schema migration without changing unrelated application tables.

Keep cleanup controlled

The cleanup script contains destructive SQL for the isolated lab resources. Saving it now gives the final cleanup section a repeatable teardown path.

  • Create cleanup.ts inside src/wholesale-lab from the Visual Studio Code Explorer sidebar.
  • Paste this cleanup script into the new file:
import "dotenv/config";
import { Pool } from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  await pool.query(`
    drop table if exists wholesale_lab_payments;
    drop table if exists wholesale_lab_sales;
    drop table if exists wholesale_lab_customers;
    drop type if exists wholesale_lab_payment_method;
    drop type if exists wholesale_lab_sales_channel;
    drop table if exists drizzle.__drizzle_wholesale_lab_migrations;
  `);
  console.log("Wholesale lab resources deleted");
} finally {
  await pool.end();
}

How does cleanup stay safe?

The script drops the child payment table before either parent table. This order respects the restrictive financial relationships.

Every deleted database object uses the wholesale_lab namespace. The migration-log table is the final isolated database object removed.

  • Save src/wholesale-lab/cleanup.ts.
  • Confirm that cleanup.ts appears beside connection.ts and demo.ts in the Visual Studio Code Explorer sidebar.
Aggregate payments per sale

One sale can now have any number of payment rows. A grouped subquery reduces those rows to one received total for each saleId.

The later report can join that total back to its sale. A left join keeps unpaid sales visible because it does not require a matching payment row.

  • Return to src/wholesale-lab/demo.ts in the Visual Studio Code window from earlier.
  • Place this grouped subquery after the existing code that prints paymentRows.
  const paymentTotals = db
    .select({
      saleId: wholesaleLabPayments.saleId,
      paidAmount: sql<string>`coalesce(sum(${wholesaleLabPayments.amount}), 0)`.as(
        "paid_amount",
      ),
    })
    .from(wholesaleLabPayments)
    .groupBy(wholesaleLabPayments.saleId)
    .as("payment_totals");

What does the subquery calculate?

  • The grouping produces one result for each sale represented in the payment ledger.
  • The sum expression adds every payment amount in that group.
  • The coalesce expression supplies zero when the joined sale has no payment total.
  • The alias lets the next query join this calculated result as payment_totals.
  • Save src/wholesale-lab/demo.ts.

Before you rerun the script, do you expect the existing two payment rows to remain intact after adding the grouped subquery?

  • Check the existing payment output by running this command:
npx tsx src/wholesale-lab/demo.ts

What should remain visible?

You should still see one 8000.00 payment using CASH. You should also see one 5000.00 payment using CARD.

Those rows confirm that the ledger remains the source of each receipt event. The grouped subquery can now turn their amounts into a sale-level total.

Did the payment output stop working?

Check that sql remains imported beside eq from drizzle-orm. Confirm that the subquery sits inside the existing try block.

Help me fix the paymentTotals subquery while preserving the two existing payment rows.

Derive the receivables summary

A receivables report needs three monetary views of the same sale. The sale total represents revenue while the payment sum represents cash received.

The outstanding balance is their difference. The payment state follows from that calculation each time the report runs.

  • Add this summary query after paymentTotals inside src/wholesale-lab/demo.ts.
  const [summary] = await db
    .select({
      soldAmount: wholesaleLabSales.totalAmount,
      paidAmount: sql<string>`coalesce(${paymentTotals.paidAmount}, 0)`,
      outstandingAmount: sql<string>`${wholesaleLabSales.totalAmount} - coalesce(${paymentTotals.paidAmount}, 0)`,
      paymentStatus: sql<string>`case
        when coalesce(${paymentTotals.paidAmount}, 0) = 0 then 'UNPAID'
        when coalesce(${paymentTotals.paidAmount}, 0) < ${wholesaleLabSales.totalAmount} then 'PARTIALLY_PAID'
        else 'PAID'
      end`,
    })
    .from(wholesaleLabSales)
    .leftJoin(paymentTotals, eq(wholesaleLabSales.id, paymentTotals.saleId))
    .where(eq(wholesaleLabSales.id, saleId));

  console.table([summary]);

How is the balance derived?

  • The query reads soldAmount from the sale instead of duplicating revenue in another table.
  • The query reads paidAmount from the grouped payment ledger.
  • The query subtracts received cash from the sale total to calculate outstandingAmount.
  • The case expression returns UNPAID when nothing has been received.
  • The same expression returns PARTIALLY_PAID while the received total remains below the sale total.
  • The remaining result becomes PAID.

Why derive the payment state?

A stored payment status can drift away from the ledger whenever another receipt is inserted. A derived state always reflects the current sale total and payment sum.

This keeps the ledger authoritative. Every report recomputes the business answer from its source records.

  • Save src/wholesale-lab/demo.ts.

Before you run the final check, what balance should remain after the two receipt amounts are subtracted from the sale total?

  • Run the finished receivables demonstration with this command:
npx tsx src/wholesale-lab/demo.ts

What should the final report show?

You should see 20000.00 sold and 13000.00 paid. You should see 7000.00 outstanding.

The payment state should read PARTIALLY_PAID. That result proves the report derives the balance from both preserved payment rows.

Does the summary look wrong?

Confirm that the summary joins paymentTotals through saleId. Check that both payment inserts remain inside the transaction.

Help me debug the wholesale receivables summary and compare the sold, paid, and outstanding values.

✔️ Awesome, I've got everything!

Your lab now protects its financial rows and calculates the current receivables position from the payment ledger. You have a tested blueprint for the follow-on integration project.

ⓧ I'd like to double check the full code

Compare each lab file with the final reference below. Keep the existing isolated migration folder generated by Drizzle Kit.

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/wholesale-lab/schema.ts",
  out: "./drizzle-wholesale",
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
  migrations: {
    table: "__drizzle_wholesale_lab_migrations",
    schema: "drizzle",
  },
});

This configuration keeps the schema path and migration history separate from the application's normal Drizzle setup.

import "dotenv/config";
import { Pool } from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  const result = await pool.query<{ server_time: Date }>(
    "select now() as server_time",
  );
  console.log("PostgreSQL connection successful", result.rows[0].server_time);
} finally {
  await pool.end();
}

This unchanged connection check confirms that the isolated lab still targets the selected PostgreSQL database.

import { sql } from "drizzle-orm";
import {
  check,
  index,
  numeric,
  pgEnum,
  snakeCase,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const wholesaleLabSalesChannel = pgEnum(
  "wholesale_lab_sales_channel",
  ["RETAIL", "WHOLESALE"],
);

export const wholesaleLabPaymentMethod = pgEnum(
  "wholesale_lab_payment_method",
  ["CASH", "CARD", "EASYPAISA"],
);

export const wholesaleLabCustomers = snakeCase.table(
  "wholesale_lab_customers",
  {
    id: uuid().primaryKey().defaultRandom(),
    name: varchar({ length: 200 }).notNull(),
    phone: varchar({ length: 30 }),
    address: text(),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_customers_name_not_blank",
      sql`length(trim(${table.name})) > 0`,
    ),
  ],
);

export const wholesaleLabSales = snakeCase.table(
  "wholesale_lab_sales",
  {
    id: uuid().primaryKey().defaultRandom(),
    customerId: uuid().references(() => wholesaleLabCustomers.id, {
      onDelete: "restrict",
    }),
    salesChannel: wholesaleLabSalesChannel().default("RETAIL").notNull(),
    totalAmount: numeric({ precision: 10, scale: 2 }).notNull(),
    dueAt: timestamp({ withTimezone: true }),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_sales_total_positive",
      sql`${table.totalAmount} > 0`,
    ),
    check(
      "wholesale_lab_sales_customer_required",
      sql`${table.salesChannel} <> 'WHOLESALE' OR ${table.customerId} IS NOT NULL`,
    ),
    index("wholesale_lab_sales_customer_idx").on(table.customerId),
    index("wholesale_lab_sales_due_at_idx").on(table.dueAt),
  ],
);

export const wholesaleLabPayments = snakeCase.table(
  "wholesale_lab_payments",
  {
    id: uuid().primaryKey().defaultRandom(),
    saleId: uuid()
      .notNull()
      .references(() => wholesaleLabSales.id, { onDelete: "restrict" }),
    amount: numeric({ precision: 10, scale: 2 }).notNull(),
    method: wholesaleLabPaymentMethod().notNull(),
    receivedAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
    reference: text(),
    createdAt: timestamp({ withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    check(
      "wholesale_lab_payments_amount_positive",
      sql`${table.amount} > 0`,
    ),
    index("wholesale_lab_payments_sale_idx").on(table.saleId),
    index("wholesale_lab_payments_received_at_idx").on(table.receivedAt),
  ],
);

This schema contains the final enums and three isolated tables. Its checks and indexes protect the receivables model.

import "dotenv/config";
import { eq, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import {
  wholesaleLabCustomers,
  wholesaleLabPayments,
  wholesaleLabSales,
} from "./schema";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle({ client: pool });

try {
  const saleId = await db.transaction(async (tx) => {
    const [customer] = await tx
      .insert(wholesaleLabCustomers)
      .values({
        name: `Demo Corner Shop ${Date.now()}`,
        phone: "0300-0000000",
      })
      .returning({ id: wholesaleLabCustomers.id });

    const [sale] = await tx
      .insert(wholesaleLabSales)
      .values({
        customerId: customer.id,
        salesChannel: "WHOLESALE",
        totalAmount: "20000.00",
        dueAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000),
      })
      .returning({ id: wholesaleLabSales.id });

    await tx.insert(wholesaleLabPayments).values([
      {
        saleId: sale.id,
        amount: "8000.00",
        method: "CASH",
        reference: "First partial payment",
      },
      {
        saleId: sale.id,
        amount: "5000.00",
        method: "CARD",
        reference: "Second partial payment",
      },
    ]);

    return sale.id;
  });

  const paymentRows = await db
    .select({
      amount: wholesaleLabPayments.amount,
      method: wholesaleLabPayments.method,
      receivedAt: wholesaleLabPayments.receivedAt,
    })
    .from(wholesaleLabPayments)
    .where(eq(wholesaleLabPayments.saleId, saleId));

  console.table(paymentRows);

  const paymentTotals = db
    .select({
      saleId: wholesaleLabPayments.saleId,
      paidAmount: sql<string>`coalesce(sum(${wholesaleLabPayments.amount}), 0)`.as(
        "paid_amount",
      ),
    })
    .from(wholesaleLabPayments)
    .groupBy(wholesaleLabPayments.saleId)
    .as("payment_totals");

  const [summary] = await db
    .select({
      soldAmount: wholesaleLabSales.totalAmount,
      paidAmount: sql<string>`coalesce(${paymentTotals.paidAmount}, 0)`,
      outstandingAmount: sql<string>`${wholesaleLabSales.totalAmount} - coalesce(${paymentTotals.paidAmount}, 0)`,
      paymentStatus: sql<string>`case
        when coalesce(${paymentTotals.paidAmount}, 0) = 0 then 'UNPAID'
        when coalesce(${paymentTotals.paidAmount}, 0) < ${wholesaleLabSales.totalAmount} then 'PARTIALLY_PAID'
        else 'PAID'
      end`,
    })
    .from(wholesaleLabSales)
    .leftJoin(paymentTotals, eq(wholesaleLabSales.id, paymentTotals.saleId))
    .where(eq(wholesaleLabSales.id, saleId));

  console.table([summary]);
} finally {
  await pool.end();
}

This demonstration inserts two immutable receipts before calculating the sale-level summary. Its payment status is derived from the ledger.

import "dotenv/config";
import { Pool } from "pg";

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is required");
}

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  await pool.query(`
    drop table if exists wholesale_lab_payments;
    drop table if exists wholesale_lab_sales;
    drop table if exists wholesale_lab_customers;
    drop type if exists wholesale_lab_payment_method;
    drop type if exists wholesale_lab_sales_channel;
    drop table if exists drizzle.__drizzle_wholesale_lab_migrations;
  `);
  console.log("Wholesale lab resources deleted");
} finally {
  await pool.end();
}

This cleanup script removes every persistent database object created by the isolated lab. It leaves unrelated application tables untouched.

That completes the database lab. You can now demonstrate exactly what the shop bought and what it paid with the balance it still owes.

Secret mission

Find Overdue Shop Balances

Build a collection report that finds past-due wholesale sales with money still outstanding. Derive the overdue state from the due date and payment ledger.

Clean Up Your Resources

Clean Up Your Resources

Your wholesale lab remains in your safe development PostgreSQL database at a cost of $0. Choose whether to keep it ready, pause your work, or remove it completely.

Resources you used:

  • Three PostgreSQL tables named wholesale_lab_customers, wholesale_lab_sales, and wholesale_lab_payments.
  • Two PostgreSQL enum types named wholesale_lab_sales_channel and wholesale_lab_payment_method.
  • A Drizzle migration tracking table named drizzle.__drizzle_wholesale_lab_migrations.
  • A separate migration folder at drizzle-wholesale.
  • The wholesale lab source directory at src/wholesale-lab.
  • The isolated Drizzle configuration file named drizzle.wholesale.config.ts.

Keep everything running

No action is needed. Choose this option if you want the lab as a tested blueprint for the follow-on integration project.

  • Retain the three lab tables with their demonstration rows.
  • Retain the two enum types that protect sales channels and payment methods.
  • Preserve drizzle.__drizzle_wholesale_lab_migrations as the isolated migration history.
  • Keep drizzle-wholesale for future schema comparisons.
  • Keep src/wholesale-lab with drizzle.wholesale.config.ts as your validated receivables blueprint.

Pause - I'll come back to this later

Pausing stops any active Node.js process. Every table and file stays available at no local service charge.

  • Return to the PowerShell session from earlier.
  • Confirm the PowerShell prompt is available.
  • Press Ctrl+C if an active Node.js process still owns the terminal.
  • Leave all wholesale lab resources in place.

Delete - I don't want to use this again

Deleting the lab is irreversible. The cleanup script targets only the isolated wholesale resources.

  • Return to the PowerShell session connected to your safe development PostgreSQL database.
  • Remove the lab database objects by running this command:
npx tsx src/wholesale-lab/cleanup.ts

What Does the Cleanup Remove?

  • The script drops wholesale_lab_payments before removing its parent sale records.
  • It drops the sales table before removing its parent customer records.
  • It removes both wholesale lab enum types after their dependent tables are gone.
  • It removes drizzle.__drizzle_wholesale_lab_migrations from the development database.
  • Confirm PowerShell prints Wholesale lab resources deleted.

Cleanup Script Did Not Finish?

  • Keep the local lab files until the cleanup script completes.
  • Confirm PowerShell is at the project folder that contains drizzle.wholesale.config.ts.
  • Check that DATABASE_URL still reaches the safe development database.

Help me troubleshoot the wholesale cleanup script.

  • Return to the project window in Visual Studio Code.
  • Remove src/wholesale-lab through the project's file tree.
  • Remove drizzle-wholesale through the project's file tree.
  • Remove drizzle.wholesale.config.ts through the project's file tree.
  • Confirm all three paths are absent from the project's file tree.

Nice Work!

Nice Work!

Mission complete! You've built an isolated wholesale accounts receivable lab in PostgreSQL.

What you learned:

  • Designed lab tables with positive-amount checks. Protected financial records with restrictive foreign keys. Added targeted indexes. Generated isolated migrations with Drizzle ORM.
  • Demonstrated the single-payment failure when CARD overwrote the earlier CASH method. Replaced that field with a payment ledger that retains each amount, method, and receipt time.
  • Produced a receivables summary showing 20000.00 sold. It shows 13000.00 paid. It calculates 7000.00 outstanding with PARTIALLY_PAID derived from the data.
  • Secret Mission: Identified overdue shop balances using a past dueAt value plus a positive calculated balance. Derived OVERDUE without storing another status column.

Ready to quiz yourself?