Database Schema

Overview

The identiPay backend uses PostgreSQL with Drizzle ORM for type-safe database operations. The schema is designed to maintain user privacy while enabling efficient payment processing.

Schema Definition

The database schema is defined in src/db/schema.ts using Drizzle ORM:

src/db/schema.ts

import {
  pgTable,
  uuid,
  varchar,
  char,
  boolean,
  timestamp,
  jsonb,
  text,
  integer,
  pgEnum,
  index,
} from "drizzle-orm/pg-core";

Tables

Merchants

Stores registered merchant accounts:

export const merchants = pgTable("merchants", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 255 }).notNull(),
  suiAddress: varchar("sui_address", { length: 66 }).notNull(),
  hostname: varchar("hostname", { length: 255 }).unique().notNull(),
  did: varchar("did", { length: 512 }).unique().notNull(),
  publicKey: char("public_key", { length: 64 }).notNull(),
  apiKeyHash: char("api_key_hash", { length: 64 }).notNull(),
  active: boolean("active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

uuid

Unique merchant identifier (auto-generated)

name

varchar(255)

Merchant display name

suiAddress

varchar(66)

Merchant’s Sui blockchain address for receiving payments

hostname

varchar(255)

Merchant’s domain name (unique constraint)

did

varchar(512)

Decentralized identifier for the merchant

publicKey

char(64)

Merchant’s public key (hex-encoded)

apiKeyHash

char(64)

Hashed API key for authentication

active

boolean

default:"true"

Whether the merchant account is active

Proposals

Stores payment proposals created by merchants:

export const proposalStatusEnum = pgEnum("proposal_status", [
  "pending",
  "settled",
  "expired",
  "cancelled",
]);

export const proposals = pgTable(
  "proposals",
  {
    transactionId: uuid("transaction_id").primaryKey().defaultRandom(),
    merchantId: uuid("merchant_id")
      .references(() => merchants.id)
      .notNull(),
    proposalJson: jsonb("proposal_json").notNull(),
    intentHash: char("intent_hash", { length: 64 }).unique().notNull(),
    status: proposalStatusEnum("status").default("pending").notNull(),
    expiresAt: timestamp("expires_at").notNull(),
    suiTxDigest: varchar("sui_tx_digest", { length: 66 }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
  },
  (table) => [
    index("proposals_intent_hash_idx").on(table.intentHash),
    index("proposals_status_idx").on(table.status),
    index("proposals_merchant_id_idx").on(table.merchantId),
  ],
);

transactionId

uuid

Unique transaction identifier (auto-generated)

merchantId

uuid

Foreign key reference to merchants.id

proposalJson

jsonb

Full proposal details (items, amount, constraints)

intentHash

char(64)

Hash of the payment intent (unique constraint)

status

enum

Proposal status: pending, settled, expired, or cancelled

expiresAt

timestamp

Expiration timestamp for the proposal

suiTxDigest

varchar(66)

Sui transaction digest (set after settlement)

Indexes:

intent_hash (for quick lookup by intent)
status (for filtering active proposals)
merchant_id (for merchant transaction history)

Names

Caches registered user names and their public keys:

export const names = pgTable("names", {
  name: varchar("name", { length: 20 }).primaryKey(),
  spendPubkey: char("spend_pubkey", { length: 64 }).notNull(),
  viewPubkey: char("view_pubkey", { length: 64 }).notNull(),
  identityCommitment: char("identity_commitment", { length: 64 }).notNull(),
  onChainObjectId: varchar("on_chain_object_id", { length: 66 }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

name

varchar(20)

Unique username (3-20 characters)

spendPubkey

char(64)

User’s spend public key (hex-encoded)

viewPubkey

char(64)

User’s view public key for stealth addresses (hex-encoded)

identityCommitment

char(64)

Hash commitment to identity attributes

onChainObjectId

varchar(66)

Sui object ID for the on-chain name registration

This is a cache-only table. No buyer address is stored. No foreign key to announcements table to preserve privacy.

Announcements

Stores stealth address announcements for payment notifications:

export const announcements = pgTable(
  "announcements",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    ephemeralPubkey: char("ephemeral_pubkey", { length: 64 }).notNull(),
    viewTag: integer("view_tag").notNull(),
    stealthAddress: varchar("stealth_address", { length: 66 }).notNull(),
    metadata: text("metadata"),
    txDigest: varchar("tx_digest", { length: 66 }).notNull(),
    timestamp: timestamp("timestamp").defaultNow().notNull(),
  },
  (table) => [
    index("announcements_view_tag_idx").on(table.viewTag),
    index("announcements_timestamp_idx").on(table.timestamp),
    index("announcements_stealth_address_idx").on(table.stealthAddress),
  ],
);

uuid

Unique announcement identifier (auto-generated)

ephemeralPubkey

char(64)

Ephemeral public key for shared secret derivation

viewTag

integer

View tag for efficient announcement scanning (optimization)

stealthAddress

varchar(66)

Generated stealth address for the payment

metadata

text

Optional encrypted metadata (memo, receipt data)

txDigest

varchar(66)

Sui transaction digest that created this announcement

Indexes:

view_tag (for efficient scanning by tag)
timestamp (for chronological queries)
stealth_address (for balance queries)

Privacy Invariant: This table has NO foreign key to the names table. Announcements never link to identities.

Event Cursors

Tracks event polling position for reliable indexing:

export const eventCursors = pgTable("event_cursors", {
  eventType: varchar("event_type", { length: 128 }).primaryKey(),
  txDigest: varchar("tx_digest", { length: 66 }).notNull(),
  eventSeq: varchar("event_seq", { length: 20 }).notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

eventType

varchar(128)

Event type identifier (e.g., settlement::SettlementEvent)

txDigest

varchar(66)

Last processed transaction digest

eventSeq

varchar(20)

Last processed event sequence number

Used for cursor-based event polling, replacing unreliable WebSocket subscriptions.

Pay Requests

Stores peer-to-peer payment requests:

export const payRequestStatusEnum = pgEnum("pay_request_status", [
  "pending",
  "paid",
  "expired",
  "cancelled",
]);

export const payRequests = pgTable("pay_requests", {
  requestId: uuid("request_id").primaryKey().defaultRandom(),
  recipientName: varchar("recipient_name", { length: 20 }).notNull(),
  amount: varchar("amount", { length: 78 }).notNull(),
  currency: varchar("currency", { length: 10 }).notNull(),
  memo: text("memo"),
  expiresAt: timestamp("expires_at").notNull(),
  status: payRequestStatusEnum("status").default("pending").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

requestId

uuid

Unique payment request identifier (auto-generated)

recipientName

varchar(20)

Username of the payment recipient

amount

varchar(78)

Payment amount (stored as string to preserve precision)

currency

varchar(10)

Currency code (e.g., “USDC”)

memo

text

Optional payment memo/note

status

enum

Request status: pending, paid, expired, or cancelled

Enums

Proposal Status

export const proposalStatusEnum = pgEnum("proposal_status", [
  "pending",   // Waiting for payment
  "settled",   // Payment completed on-chain
  "expired",   // Proposal expired before payment
  "cancelled", // Manually cancelled by merchant
]);

Pay Request Status

export const payRequestStatusEnum = pgEnum("pay_request_status", [
  "pending",   // Waiting for payment
  "paid",      // Payment received
  "expired",   // Request expired
  "cancelled", // Cancelled by requester
]);

Migrations

Generate Migration

When you modify the schema, generate a migration:

deno task db:generate

This creates a SQL migration file in ./drizzle/ directory.

Apply Migration

Apply pending migrations to the database:

deno task db:migrate

Manual Migration

You can also apply migrations manually:

-- Example: Add index to proposals table
CREATE INDEX proposals_created_at_idx ON proposals(created_at);

Database Queries

Using Drizzle ORM

import { eq } from "drizzle-orm";

const [proposal] = await db
  .select()
  .from(proposals)
  .where(eq(proposals.intentHash, intentHash))
  .limit(1);

Privacy Considerations

Data Minimization

No user addresses stored: Only stealth addresses (one-time use)
No identity linkage: Announcements table has no foreign keys to identities
Cached public keys: Names table caches public data only

Separation of Concerns

Merchants Table
└─ Proposals Table (FK: merchantId)

Names Table (isolated cache)

Announcements Table (isolated, no FKs)

This schema design ensures that:

Merchant transactions cannot be linked to specific users
User identities cannot be derived from announcements
Payment privacy is preserved at the database level

Backup and Maintenance

Backup Database

pg_dump -U identipay_user identipay > identipay_backup.sql

Restore Database

psql -U identipay_user identipay < identipay_backup.sql

Vacuum and Analyze

VACUUM ANALYZE proposals;
VACUUM ANALYZE announcements;

Performance Tips

Index Usage

Use indexed columns in WHERE clauses:

// Good: Uses index
.where(eq(proposals.intentHash, hash))

// Bad: No index on proposalJson
.where(sql`proposal_json->>'amount' = '100'`)

Query Optimization

Limit result sets:

.limit(100) // Prevent large result sets

Use select specific columns:

.select({ id: proposals.transactionId, status: proposals.status })

Connection Pooling

The backend uses postgres library with connection pooling:

import postgres from "postgres";

export function createDb(databaseUrl: string) {
  const client = postgres(databaseUrl, {
    max: 10, // Maximum pool size
    idle_timeout: 20,
  });
  
  const db = drizzle(client);
  return { db, client };
}

Merchant Integration

Mobile Applications

Backend Setup

Overview

Schema Definition

Tables

Merchants

Proposals

Names

Announcements

Event Cursors

Pay Requests

Enums

Proposal Status

Pay Request Status

Migrations

Generate Migration

Apply Migration

Manual Migration

Database Queries

Using Drizzle ORM

Privacy Considerations

Data Minimization

Separation of Concerns

Backup and Maintenance

Backup Database

Restore Database

Vacuum and Analyze

Performance Tips

Index Usage

Query Optimization

Connection Pooling

Next Steps

Sui Configuration

API Reference

Build docs developers (and LLMs) love

Merchant Integration

Mobile Applications

Backend Setup

​Overview

​Schema Definition

​Tables

​Merchants

​Proposals

​Names

​Announcements

​Event Cursors

​Pay Requests

​Enums

​Proposal Status

​Pay Request Status

​Migrations

​Generate Migration

​Apply Migration

​Manual Migration

​Database Queries

​Using Drizzle ORM

​Privacy Considerations

​Data Minimization

​Separation of Concerns

​Backup and Maintenance

​Backup Database

​Restore Database

​Vacuum and Analyze

​Performance Tips

​Index Usage

​Query Optimization

​Connection Pooling

​Next Steps

Sui Configuration

API Reference

Build docs developers (and LLMs) love

Overview

Schema Definition

Tables

Merchants

Proposals

Names

Announcements

Event Cursors

Pay Requests

Enums

Proposal Status

Pay Request Status

Migrations

Generate Migration

Apply Migration

Manual Migration

Database Queries

Using Drizzle ORM

Privacy Considerations

Data Minimization

Separation of Concerns

Backup and Maintenance

Backup Database

Restore Database

Vacuum and Analyze

Performance Tips

Index Usage

Query Optimization

Connection Pooling

Next Steps