Database model - Application database schema and relationships

schemas/
├── auth.ts              # Authentication and session management
├── kyc.ts               # KYC/AML verification workflows
├── settings.ts          # User preferences and configuration
└── exchange-rates.ts    # Currency conversion rates cache

// kit/dapp/src/lib/db/schemas/kyc.ts
import { pgTable, text, timestamp, boolean, jsonb } from "drizzle-orm/pg-core";
import { users } from "./auth";

export const kycApplications = pgTable("kyc_applications", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  status: text("status", {
    enum: [
      "draft",
      "submitted",
      "under_review",
      "approved",
      "rejected",
      "expired",
    ],
  }).notNull(),
  firstName: text("first_name").notNull(),
  lastName: text("last_name").notNull(),
  dateOfBirth: text("date_of_birth").notNull(), // Stored as ISO 8601 string
  nationality: text("nationality").notNull(),
  residenceCountry: text("residence_country").notNull(),
  residenceAddress: jsonb("residence_address")
    .$type<{
      street: string;
      city: string;
      state?: string;
      postalCode: string;
      country: string;
    }>()
    .notNull(),
  submittedAt: timestamp("submitted_at"),
  reviewedAt: timestamp("reviewed_at"),
  reviewedBy: text("reviewed_by").references(() => users.id),
  rejectionReason: text("rejection_reason"),
  expiresAt: timestamp("expires_at"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

import type { InferSelectModel, InferInsertModel } from "drizzle-orm";
import { kycApplications } from "./schemas/kyc";

// Type for querying
type KYCApplication = InferSelectModel<typeof kycApplications>;

// Type for inserting
type NewKYCApplication = InferInsertModel<typeof kycApplications>;

// Use in queries
const application: KYCApplication = await db
  .select()
  .from(kycApplications)
  .where(eq(kycApplications.id, applicationId))
  .get();

import { db } from "@/lib/db";
import { kycApplications, kycDocuments } from "@/lib/db/schemas/kyc";
import { eq, and, desc } from "drizzle-orm";

// Simple select
const pending = await db
  .select()
  .from(kycApplications)
  .where(eq(kycApplications.status, "submitted"))
  .orderBy(desc(kycApplications.submittedAt));

// Join with related table
const withDocuments = await db
  .select({
    application: kycApplications,
    documents: kycDocuments,
  })
  .from(kycApplications)
  .leftJoin(kycDocuments, eq(kycApplications.id, kycDocuments.applicationId))
  .where(eq(kycApplications.userId, userId));

// Transaction
await db.transaction(async (tx) => {
  await tx
    .update(kycApplications)
    .set({ status: "approved", reviewedAt: new Date() })
    .where(eq(kycApplications.id, applicationId));

  await tx.insert(kycStatusHistory).values({
    applicationId,
    fromStatus: "submitted",
    toStatus: "approved",
    changedBy: reviewerId,
  });
});

cd kit/dapp

# Generate migration
bun run db:generate

-- drizzle/0008_add_kyc_expiration.sql
ALTER TABLE "kyc_applications" ADD COLUMN "expires_at" timestamp;
CREATE INDEX "kyc_applications_expires_at_idx" ON "kyc_applications" ("expires_at");

# Apply all pending migrations
bun run db:migrate

# Apply specific migration
bun run db:migrate --to 0008

-- Manually create rollback script
ALTER TABLE "kyc_applications" DROP COLUMN "expires_at";
DROP INDEX "kyc_applications_expires_at_idx";

// Composite index for common filter
export const kycApplications = pgTable(
  "kyc_applications",
  {
    // ... columns
  },
  (table) => ({
    statusSubmittedIdx: index("kyc_status_submitted_idx").on(
      table.status,
      table.submittedAt
    ),
  })
);

[databases]
atk = host=postgres port=5432 dbname=atk

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

EXPLAIN ANALYZE
SELECT *
FROM kyc_applications
WHERE status = 'submitted'
  AND submitted_at > NOW() - INTERVAL '7 days'
ORDER BY submitted_at DESC
LIMIT 20;

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
archive_timeout = 300  # Force WAL switch every 5 minutes

# Restore base backup
pg_restore -d atk /backup/base/latest.dump

# Apply WAL logs up to target time
recovery_target_time = '2025-01-15 14:30:00'

-- Enable slow query logging
ALTER DATABASE atk SET log_min_duration_statement = 100;  -- Log queries >100ms

-- View slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC
LIMIT 20;

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size,
  n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

SELECT
  state,
  COUNT(*) AS connections
FROM pg_stat_activity
WHERE datname = 'atk'
GROUP BY state;

-- Enable RLS on user-specific tables
ALTER TABLE user_settings ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only access their own settings
CREATE POLICY user_settings_isolation ON user_settings
  FOR ALL
  USING (user_id = current_setting('app.user_id'));

CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  operation TEXT NOT NULL,  -- INSERT, UPDATE, DELETE
  old_values JSONB,
  new_values JSONB,
  changed_by TEXT NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Trigger on sensitive tables
CREATE TRIGGER kyc_audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON kyc_applications
  FOR EACH ROW EXECUTE FUNCTION log_audit_event();