PlanetScale

Role in the system

PlanetScale is the single database for all persistent state — replacing Supabase PostgreSQL. It stores all persistent information: order records (commercial transactions), product data, metrics, records (tags, assets, suggestions), templates, combo reports, and documents.

Connection: Hyperdrive

Cloudflare Hyperdrive provides optimized connectivity between Workers and PlanetScale:

• Connection pooling — maintains persistent connections, avoiding per-request TCP/TLS setup
• Query caching — optional, for read-heavy dashboard queries
• Edge proximity — connections routed through Cloudflare’s network

wrangler.toml

[[hyperdrive]]
binding = "HYPERDRIVE"
id = "your-hyperdrive-id"

import { drizzle } from 'drizzle-orm/node-postgres';

export function getDb(env: Bindings) {
  return drizzle(env.HYPERDRIVE.connectionString);
}

ORM: Drizzle (PostgreSQL dialect)

The rewrite continues using Drizzle ORM with the PostgreSQL dialect — same dialect as the current Supabase system, now targeting PlanetScale Postgres. Table and column names follow consistent naming conventions:

import { pgTable, varchar, integer, timestamp, jsonb, boolean, text } from 'drizzle-orm/pg-core';

// Product (IOF MaterialArtifact) — physical products with causal unity
export const product = pgTable('product', {
  uid: text('uid').primaryKey(),                    // prd_xxx
  name: text('name'),
  status: jsonb('status').notNull().default({ phase: 'active' }),
  labels: jsonb('labels').notNull().default({}),
  annotations: jsonb('annotations').notNull().default({}),
  createTs: timestamp('create_ts', { withTimezone: true }).notNull().defaultNow(),
  updateTs: timestamp('update_ts', { withTimezone: true }).notNull().defaultNow(),
});

// Order Header — ERP header/line-item convention (orders as Process Records)
export const orderHeader = pgTable('order_header', {
  uid: text('uid').primaryKey(),                    // ord_xxx
  customerId: text('customer_id'),
  currencyCode: text('currency_code').notNull(),
  revision: integer('revision').notNull().default(1),
  status: jsonb('status').notNull().default({ phase: 'draft' }),
  labels: jsonb('labels').notNull().default({}),
  annotations: jsonb('annotations').notNull().default({}),
  provisionTimerTs: timestamp('provision_timer_ts', { withTimezone: true }),
  createTs: timestamp('create_ts', { withTimezone: true }).notNull().defaultNow(),
  updateTs: timestamp('update_ts', { withTimezone: true }).notNull().defaultNow(),
});

Sales measurement data (measurement.sales) and performance metrics (measurement.performance) live on R2 as Iceberg tables, not PG. See Schema Design for the complete table definitions.

Safety limits

PlanetScale enforces system-level constraints that shape all pipeline workflow executions:

Limit	Value	Design response
Rows per query	100k	Cursor-based pagination
Rows per statement	100k	Chunked batch writes
Result size	64 MiB	Selective column queries
Transaction timeout	20s	Short, independent transactions
Autocommit timeout	900s	Bounded single statements

Schema migration workflow

PlanetScale branching supports safe schema changes:

1. Create dev branch from production
2. Apply migrations via dbmate on the dev branch
3. Create deploy request (PR-like review for schema changes)
4. Merge — PlanetScale applies non-blocking DDL
5. Rollback available (undo deployment without data loss)

# Migration workflow with dbmate
dbmate new add_macrodata_artifact_table
# Edit migration SQL
dbmate up  # apply on dev branch
# Create deploy request in PlanetScale dashboard

Foreign keys

Per ADR-004, foreign keys are documented in the Drizzle schema for type safety but not enforced at the database level. Relations (like the denotation link between assets and products) are documented in naming conventions, not enforced by database constraints.

Application code enforces integrity, with periodic orphan detection in background ProcedureExecutions.

No stored procedures

Per ADR-003, all business logic lives in application code. The 10+ Supabase RPCs (get_tag_categories(), upsert_tag_classification(), refresh_tag_performance_aggregate(), etc.) are migrated to TypeScript in the Workers.