Build a Digital Marketplace with Afriex, Fastify and Postgres, Part 1: Data Model and the Storefront

Amara designs Lightroom presets in Lagos. She wants to sell a pack for ₦12,000 to buyers in Nigeria, Ghana, Kenya, and the US.

Tunde wants to buy that pack. He wants to pay with his card, get the download link in his inbox, and never create an account.

Amara wants the ₦12,000 to land in her bank account.

That is the whole product. You've probably used it before: it's Gumroad, or Selar if you're in Nigeria. This series builds a small version of the same thing, and like those platforms, it splits cleanly in two:

• Everything up to "a paid order exists" is a storefront. Products, listings, carts, orders. Mostly reads and writes to your own database.
• Everything after that is payments. Charging Tunde, tracking what you owe Amara, paying her out.

This series keeps the halves apart on purpose:

• Part 1 (this article): the four tables and the Fastify API that serve the storefront. No payment provider is involved anywhere in it.
• Part 2: wiring Afriex into what Part 1 built: Checkout to collect Tunde's money, Payouts to send Amara hers.

The full source is on GitHub: a Fastify + Postgres backend with a Next.js storefront on top. This article covers the backend.

What you'll have at the end

A running API where:

• A creator can sign in, create a product, and publish it.
• Anyone can list published products and view one, without an account.
• An order can be created for a product and later marked paid.
• A paid order hands back a download link that expires.

No money moves. That's Part 2's job, and it will not require changing a single table you build here.

Before you start

You need Node 20+, Docker, and a terminal. You should have built a REST API before, in any framework. You do not need to have used Fastify or Drizzle; I will explain everything step by step.

The repo ships a docker-compose.yml that starts Postgres (and Redis, which Part 2's payout queue will need), so there's nothing to install by hand:

git clone https://github.com/codewithveek/afriex-creator-payout
cd afriex-creator-payout
docker compose up -d        # starts Postgres and Redis

cd server
cp .env.example .env
pnpm install
pnpm db:migrate
pnpm dev                    # API on http://localhost:4000

In .env, set DATABASE_URL to match the compose credentials:

DATABASE_URL=postgresql://afriex:afriex_dev_password@localhost:5432/afriex_creator_payout

If you'd rather run your own Postgres 16+, skip the compose step and point DATABASE_URL at it instead.

Let's start from what the app has to do

Before writing any schema, let's write down the actions as a list:

1. A creator lists a product for a price in a currency.
2. A creator keeps a product hidden while they're still editing it.
3. Anyone can browse published products.
4. A buyer buys one product, with or without an account.
5. A buyer gets a download link that only works for them.
6. Later, the platform pays the creator.

Those six list items are the schema. Item 1 needs creators and products. Item 4 needs orders and an optional customers. Item 6 is Part 2.

The four tables

• creators: people who sell. This table holds the payout details Part 2 will read.
• products: what they sell.
• customers: people who bought and chose to create an account.
• orders: one row per purchase attempt.

Four design decisions in there are worth walking through slowly, because each one prevents a specific bug.

Decision 1: buyers and sellers live in separate tables

Amara is a creator. Tunde is a customer. Amara might also buy someone else's presets, so she can be both, but the platform never forces it.

More importantly, Tunde can buy without an account at all. He types his email at checkout and leaves. That's a guest checkout, and it's why orders.customer_id is nullable:

customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),

The order still stores his email and name directly, so you can email him the download link whether or not a customers row exists.

If you'd instead forced every buyer through a shared users table, you'd have to create an account for Tunde before he can pay. That's a signup form standing between a willing buyer and a checkout button. Notice that Gumroad and Selar both let you buy with nothing but an email for exactly this reason.

Decision 2: money is numeric(14, 2), never a float

Postgres has a real and a double precision type. Don't reach for either. Here's why, in a Node REPL:

> 16.08 * 3           // three copies of a $16.08 product
48.239999999999995

> 4.35 * 100          // converting $4.35 to cents
434.99999999999994

> Math.round(1.005 * 100) / 100   // rounding $1.005 to the nearest cent
1                     // not 1.01

Floating-point numbers store binary fractions. Decimal amounts like 16.08 have no exact binary form, so every arithmetic operation drifts by a fraction of a cent. Do that across a few thousand orders and a payout report stops matching the ledger.

numeric stores digits exactly:

price: numeric('price', { precision: 14, scale: 2 }).notNull(),

precision: 14 means up to 14 total digits. scale: 2 means two of them sit after the decimal point. That's up to $999,999,999,999.99 per product, more headroom than any preset pack needs, and cheap to keep.

One thing that catches people: the Postgres driver returns numeric columns to JavaScript as strings, not numbers, precisely so it doesn't lose the precision you just paid for. So product.price is "16.08". If you do product.price * 3 you're back in float land. Do money arithmetic either in SQL or with a decimal library like decimal.js.

Decision 3: currency is an enum, not a varchar

// infra/database/schema/enums.ts
export const currencyEnum = pgEnum('currency', ['USD', 'NGN', 'GHS', 'KES']);

export const orderStatusEnum = pgEnum('order_status', [
  'PENDING',
  'COMPLETED',
  'REFUNDED',
  'FAILED',
]);

A Postgres enum is a custom type with a fixed list of allowed values. Insert anything outside the list and the database rejects the row.

With a varchar currency column, nothing stops 'ngn', 'NGN ', 'Naira', and 'NGN' from all ending up in the same column, and then a payout query that filters WHERE currency = 'NGN' silently misses orders. The enum makes that impossible at the storage layer instead of hoping every code path remembers to normalize.

products.currency, orders.currency, and Part 2's payout tables all reference this same type. Adding a fifth currency means writing a migration (ALTER TYPE currency ADD VALUE 'ZAR'), which is exactly the amount of friction supporting a new corridor deserves.

In Postgres 12 and later you can add an enum value inside a transaction, but you can't use the new value until that transaction commits. Keep the ADD VALUE migration separate from any migration that writes rows with it.

Decision 4: how to search an encrypted email column

Emails are encrypted at rest, so customers.email holds ciphertext rather than tunde@example.com.

That breaks a query you will absolutely need. Tunde checks out as a guest today. Next month he creates an account with the same address, and you want to attach his old orders to it. The natural query is:

SELECT * FROM orders WHERE customer_email = 'tunde@example.com';

That returns nothing. Proper encryption is randomized: encrypting the same email twice produces two different ciphertexts, on purpose, so an attacker with the database can't tell which two customers share an address. Randomized ciphertext means equality comparison is off the table.

The fix is a blind index: a second column holding a keyed hash of the value, used only for exact-match lookups.

// infra/crypto/blind-index.ts
import { createHmac } from 'node:crypto';

export function emailBlindIndex(email: string): string {
  const normalized = email.trim().toLowerCase();
  return createHmac('sha256', BLIND_INDEX_KEY).update(normalized).digest('hex');
}

customerEmail: text('customer_email').notNull(),                                    // ciphertext
customerEmailHash: varchar('customer_email_hash', { length: 64 }).notNull().default(''), // blind index

Lookups then run against the hash, never the plaintext:

const hash = emailBlindIndex(input.email);
const previousOrders = await db.query.orders.findMany({
  where: eq(orders.customerEmailHash, hash),
});

Three details that matter:

• HMAC, not plain SHA-256. There are only so many plausible email addresses. Someone holding a table of plain hashes can grind through a wordlist and recover addresses in minutes. HMAC mixes in a secret key that lives outside the database, so a stolen dump is useless without it.
• Normalize before hashing. Tunde@Example.com and tunde@example.com must produce the same hash, or the lookup misses.
• Exact match only. You can't do LIKE '%@gmail.com' or sort alphabetically on a blind index. If you need those, you need a different design, usually a searchable subset stored separately, with its own risk tradeoff.

The products table

// infra/database/schema/products.ts
export const products = pgTable(
  'products',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    creatorId: uuid('creator_id').notNull().references(() => creators.id, { onDelete: 'cascade' }),

    name: varchar('name', { length: 255 }).notNull(),
    description: text('description'),
    price: numeric('price', { precision: 14, scale: 2 }).notNull(),
    currency: currencyEnum('currency').notNull().default('USD'),

    fileUrl: varchar('file_url', { length: 512 }),
    fileName: varchar('file_name', { length: 255 }),
    fileSize: numeric('file_size', { precision: 14, scale: 0 }),

    published: boolean('published').notNull().default(false),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    index('idx_products_creator').on(table.creatorId),
    index('idx_products_published').on(table.published),
    index('idx_products_published_created').on(table.published, table.createdAt),
    index('idx_products_creator_published').on(table.creatorId, table.published),
  ],
);

published replaces a drafts table. A product Amara is still writing copy for has published = false and never appears in public queries. One boolean, one table, nothing to keep in sync.

The indexes exist because of specific queries. Don't add indexes by vibes: every index slows down writes and costs disk. Each one here has a query behind it:

The order of columns in a composite index matters. Postgres can use (published, created_at) for a query that filters on published alone, but not for one that filters on created_at alone, because an index is usable from its leftmost column rightward. Put the column you filter for equality first, the one you sort or range-scan second.

How a request travels through the app

Every domain (products, orders, customers, and creators) has the same number of files. app.ts only bolts modules together:

// app.ts
export async function buildApp() {
  const app = Fastify({ /* ... */ });

  await app.register(productsRoutes);
  await app.register(ordersRoutes);
  await app.register(customersRoutes);

  return app;
}

Here's what each layer is allowed to do:

A controller never writes SQL. A repository never knows an HTTP request exists. That's the whole convention, and it's what lets Part 2 add a payments module without editing any of these files.

The router: the access model in one screen

// modules/products/products.router.ts
export async function productsRoutes(fastify: FastifyInstance) {
  fastify.post('/api/products', {
    preHandler: [authenticate, authorize(Role.CREATOR), validateBody(CreateProductSchema)],
    handler: productsController.create,
  });

  fastify.get('/api/products/mine', {
    preHandler: [authenticate, authorize(Role.CREATOR)],
    handler: productsController.listMyProducts,
  });

  // No preHandler. This is the public storefront feed.
  fastify.get('/api/products', {
    handler: productsController.listPublished,
  });

  fastify.get('/api/products/:id', {
    handler: productsController.getById,
  });
}

preHandler is Fastify's term for functions that run before the route handler and can reject the request early. Reading this one file tells you exactly who can access what: creating and listing your own products needs a creator session; the public feed and the product page need nothing.

The controller, and a leak it prevents

// modules/products/products.controller.ts
function sanitizeForPublic(product: Record<string, unknown>) {
  const { fileUrl, fileName, fileSize, ...rest } = product;
  return rest;
}

export const productsController = {
  async listPublished(request: FastifyRequest, reply: FastifyReply) {
    const pag = parsePagination(request.query as Record<string, unknown>);
    const { rows, total } = await productsService.getPublished(
      (pag.page - 1) * pag.pageSize,
      pag.pageSize,
    );
    return reply.code(200).send({
      data: rows.map(sanitizeForPublic),
      meta: buildPaginationMeta(pag, total),
    });
  },
};

sanitizeForPublic strips the file fields from a product before it goes out in a public response.

The same products row backs two very different responses: Amara's dashboard, where she needs to see her uploaded file, and the public feed, where fileUrl is the paid download. Forget to strip it and GET /api/products looks like this:

// leaking
{
  "id": "9d1b…",
  "name": "Lagos Golden Hour Presets",
  "price": "12000.00",
  "fileUrl": "https://cdn.example.com/files/lagos-presets.zip"  // 👈 free for everyone
}

Instead of what it should be:

{
  "id": "9d1b…",
  "name": "Lagos Golden Hour Presets",
  "price": "12000.00",
  "currency": "NGN"
}

You could solve it with a second table or a database view. Stripping the fields once, at the HTTP boundary, is less machinery, as long as it happens in one function that every public response goes through, rather than being re-remembered at each endpoint.

The service: this is where the business rules live

// modules/products/products.service.ts
async update(creatorId: string, productId: string, input: Record<string, unknown>): Promise<Product> {
  const product = await productsRepository.findById(productId);
  if (!product) throw new NotFoundError('Product not found');
  if (product.creatorId !== creatorId) throw new NotFoundError('Product not found');
  return (await productsRepository.update(productId, input))!;
}

Notice that both failures throw the same NotFoundError.

That's deliberate. If "you don't own this" returned a 403 and "no such product" returned a 404, then a creator could loop through product IDs and use the status codes to map out the catalogue: 403 means "real product, someone else's", 404 means "nothing here". Returning 404 for both tells the prober nothing. The cost is that a creator debugging their own integration sees a slightly less helpful error, a fair trade, and one worth writing in a comment so nobody "fixes" it later.

The repository: the only file that interacts with Drizzle

// modules/products/products.repository.ts
async findPublished(offset: number, limit: number): Promise<{ rows: Product[]; total: number }> {
  const rows = await db.query.products.findMany({
    where: eq(products.published, true),
    orderBy: (p, { desc }) => [desc(p.createdAt)],
    offset,
    limit,
  });
  const total = await db.$count(products, eq(products.published, true));
  return { rows, total };
}

The orders table, where the storefront stops

// infra/database/schema/orders.ts
export const orders = pgTable(
  'orders',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    productId: uuid('product_id').notNull().references(() => products.id, { onDelete: 'restrict' }),
    creatorId: uuid('creator_id').notNull().references(() => creators.id, { onDelete: 'restrict' }),
    customerId: uuid('customer_id').references(() => customers.id, { onDelete: 'set null' }),

    customerEmail: text('customer_email').notNull(),
    customerEmailHash: varchar('customer_email_hash', { length: 64 }).notNull().default(''),
    customerName: text('customer_name').notNull(),

    amount: numeric('amount', { precision: 14, scale: 2 }).notNull(),
    currency: currencyEnum('currency').notNull(),
    status: orderStatusEnum('status').notNull().default('PENDING'),

    paymentSessionId: varchar('payment_session_id', { length: 255 }).notNull().unique(),

    downloadTokenEncrypted: text('download_token_encrypted'),
    downloadTokenHash: varchar('download_token_hash', { length: 64 }),
    downloadTokenExpiresAt: timestamp('download_token_expires_at', { withTimezone: true }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
);

The order row exists before any money moves

When Tunde clicks Buy, the API writes a PENDING order first, with a paymentSessionId, and only then calls the payment provider. Later, a confirmation flips the row to COMPLETED.

That ordering does real work. paymentSessionId is unique, which makes order creation idempotent: if Tunde double-clicks Buy or his phone retries the request on a flaky connection, the second insert hits a unique-constraint violation instead of creating a second order and a second charge. Running it twice with the same session has the same effect as running it once.

Creating an order looks like this:

curl -X POST http://localhost:3000/api/orders \
  -H 'Content-Type: application/json' \
  -d '{
    "productId": "9d1b…",
    "customerEmail": "tunde@example.com",
    "customerName": "Tunde A."
  }'

{
  "data": {
    "id": "4f22…",
    "status": "PENDING",
    "amount": "12000.00",
    "currency": "NGN",
    "paymentSessionId": "sess_01HZ…"
  }
}

At this point in Part 1, paymentSessionId is generated locally. In Part 2 it becomes the ID Afriex hands back when you open a checkout session, and nothing else on this page changes.

Download access is a token, not a boolean

A paid order gets a signed download token. The raw token goes out by email or redirect; the database only ever stores downloadTokenHash. Every download request hashes the incoming token and compares.

The reason is a bad day. If someone reads your database, hashes give them nothing usable: they can't reverse a hash into a working token. Storing raw tokens would hand them working download links for every order you've ever fulfilled.

downloadTokenExpiresAt limits the other bad day: a link forwarded into a WhatsApp group stops working in 24 hours instead of forever.

The delete rules encode what each row means

productId: ... { onDelete: 'restrict' }
creatorId: ... { onDelete: 'restrict' }
customerId: ... { onDelete: 'set null' }

restrict means Postgres refuses to delete a product or creator that has orders. An order is a financial record. If Amara deletes a product, the row proving Tunde paid ₦12,000 for it must survive; you'll need it for refunds, support, and Part 2's payouts. Deactivate products; don't delete them.

set null on the customer is the opposite case. If Tunde deletes his account, the order stays (you still owe Amara for it) and simply stops pointing at a customer.

What this table deliberately doesn't know

Nowhere in orders is there a column naming a payment provider. Stripe, Paystack, Afriex Checkout: the schema can't tell which one confirmed the payment.

That's what makes Part 2 an additive change. Whatever collects the money has exactly one job against this table: flip PENDING to COMPLETED. Everything downstream (the download token, Amara's dashboard, Tunde's order history) already works off status.

Where we are

You now have a storefront that runs on its own:

• ✅ Amara can create a product and publish it
• ✅ Anyone can browse published products, without leaking the file URL
• ✅ An order can be created for a guest buyer and marked paid
• ✅ A paid order yields an expiring download link
• ❌ No money has actually changed hands

Part 2 adds exactly two things, and neither one touches the tables above:

1. A checkout module that opens an Afriex checkout session and stores its ID in orders.paymentSessionId.
2. A webhook handler that verifies Afriex's callback and flips orders.status to COMPLETED.

Then Amara's payouts. This is also where the platform comparison shows the true capabilities of Afriex: Selar pays creators out locally, but Afriex disburses across 30+ countries (bank transfer in Nigeria, mobile money in Kenya or Ghana, SWIFT to the US or UK), so the same payout module works whether Amara banks in Lagos or her co-creator banks in Nairobi.

In the next part of this series, you will learn how to integrate the Afriex Business API into everything built here: creating hosted checkout sessions so buyers can pay by card, bank transfer, or mobile money, handling the webhook that confirms a payment and completes the order, splitting each sale into the platform fee and the creator's earnings, verifying a creator's bank account before any money is sent to it, and disbursing payouts on demand or on a schedule. See you in Part 2.