Schema Design

nSelf table conventions, naming rules, column standards, soft-delete, audit log, and JSONB patterns — everything you need to design schemas that work with Hasura and the nSelf plugin ecosystem.

Quick Start

# Generate a migration from a Drizzle schema file
nself db migrate generate --name add_documents_table

# Apply pending migrations
nself db migrate up

# Check schema status
nself db schema

# Show diff between code and database
nself db schema diff

Architecture Overview

Schema pipeline:

  Drizzle Schema (TypeScript)
    src/db/schema/*.ts
         |
         v
  nself db migrate generate
         |
         v
  migrations/ directory
  (SQL files, versioned)
         |
         v
  nself db migrate up
         |
         v
  PostgreSQL (np_* tables)
         |
         v
  Hasura (auto-tracks tables)
         |
         v
  GraphQL API (auto-generated)

Table Naming Conventions

The np_* Prefix

All nSelf-managed tables use the np_ prefix. This creates a clear namespace that:

• Separates nSelf system tables from user-created tables
• Enables per-app isolation across a shared Postgres instance
• Makes it obvious which tables Hasura auto-tracks
• Aligns with nSelf plugin conventions (every plugin prefixes its tables)

Naming Rules

• Always snake_case — never camelCase or PascalCase in SQL
• Table names are always plural (np_users not np_user)
• Column names are always singular (email not emails)
• Junction tables: np_user_roles, np_doc_tags (entity1_entity2)
• Avoid abbreviations — source_account_id not src_acc_id

Standard Column Set

Every np_* table starts with this set. Drizzle schema example:

import { pgTable, uuid, text, timestamp } from 'drizzle-orm/pg-core'

export const npExample = pgTable('np_example', {
  // Primary key: always UUID, always gen_random_uuid()
  id: uuid('id').primaryKey().defaultRandom(),

  // Multi-app isolation (see Multi-Tenancy docs)
  sourceAccountId: text('source_account_id').notNull().default('primary'),

  // Cloud SaaS tenancy — only for nSelf Cloud operators
  // NEVER use this for multi-app isolation
  tenantId: uuid('tenant_id'),

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

  // Soft-delete: NULL = active, timestamp = deleted
  deletedAt: timestamp('deleted_at', { withTimezone: true }),

  // Your columns below
  name: text('name').notNull(),
})

Soft-Delete Pattern

Never hard-delete production data. Use deleted_at to mark rows as deleted.

-- Mark deleted
UPDATE np_users SET deleted_at = NOW() WHERE id = $1;

-- Active-record filter (required in all queries)
SELECT * FROM np_users
WHERE deleted_at IS NULL AND id = $1;

-- Partial index: fast active-record queries
CREATE INDEX idx_np_users_active
  ON np_users (id) WHERE deleted_at IS NULL;

-- Composite: common access pattern
CREATE INDEX idx_np_users_source_active
  ON np_users (source_account_id, email)
  WHERE deleted_at IS NULL;

-- RLS: GraphQL never returns deleted rows
CREATE POLICY "hide_deleted" ON np_users
  USING (deleted_at IS NULL);

-- Scheduled purge via pg_cron (after retention period)
SELECT cron.schedule('purge-deleted-users', '0 3 * * 0',
  $$DELETE FROM np_users WHERE deleted_at < NOW() - INTERVAL '90 days'$$);

In Drizzle

// Soft delete
await db.update(npUsers)
  .set({ deletedAt: new Date() })
  .where(eq(npUsers.id, userId))

// Always filter deleted in queries
const user = await db.select()
  .from(npUsers)
  .where(and(
    eq(npUsers.id, userId),
    isNull(npUsers.deletedAt)
  ))
  .limit(1)

Audit Log Pattern

Use the nself-audit plugin (free tier) for automatic change tracking. Manual implementation:

CREATE TABLE np_audit_log (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name   TEXT NOT NULL,
  record_id    UUID NOT NULL,
  action       TEXT NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  old_data     JSONB,
  new_data     JSONB,
  changed_by   UUID,                    -- user_id who made the change
  changed_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for per-record history lookup
CREATE INDEX idx_np_audit_record ON np_audit_log (table_name, record_id, changed_at DESC);

-- Trigger function
CREATE OR REPLACE FUNCTION np_audit_trigger() RETURNS trigger AS $$
BEGIN
  INSERT INTO np_audit_log (table_name, record_id, action, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) ELSE NULL END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Attach to any table that needs auditing
CREATE TRIGGER np_users_audit
  AFTER INSERT OR UPDATE OR DELETE ON np_users
  FOR EACH ROW EXECUTE FUNCTION np_audit_trigger();

updated_at Trigger

-- Generic updated_at function (created once, reused everywhere)
CREATE OR REPLACE FUNCTION np_set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to every table with updated_at
CREATE TRIGGER np_users_updated_at
  BEFORE UPDATE ON np_users
  FOR EACH ROW EXECUTE FUNCTION np_set_updated_at();

In Drizzle

// Drizzle does not auto-trigger updated_at — use the SQL trigger above,
// or update it explicitly:
await db.update(npUsers)
  .set({ name: 'New Name', updatedAt: new Date() })
  .where(eq(npUsers.id, userId))

Foreign Keys and Indexes

CREATE TABLE np_documents (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_account_id TEXT NOT NULL DEFAULT 'primary',
  user_id           UUID NOT NULL REFERENCES np_users(id) ON DELETE RESTRICT,
  category_id       UUID REFERENCES np_categories(id) ON DELETE SET NULL,
  title             TEXT NOT NULL,
  body              TEXT,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at        TIMESTAMPTZ
);

-- Index every foreign key — required, audited by nself doctor
CREATE INDEX idx_np_documents_user ON np_documents (user_id);
CREATE INDEX idx_np_documents_category ON np_documents (category_id)
  WHERE category_id IS NOT NULL;

-- Composite for common access pattern
CREATE INDEX idx_np_documents_source_user
  ON np_documents (source_account_id, user_id)
  WHERE deleted_at IS NULL;

FK ON DELETE Rules

JSONB for Flexible Data

-- Plugin config: arbitrary key-value per record
CREATE TABLE np_plugin_config (
  plugin_name   TEXT PRIMARY KEY,
  settings      JSONB NOT NULL DEFAULT '{}',
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- GIN index for containment queries
CREATE INDEX idx_np_plugin_config_settings
  ON np_plugin_config USING gin(settings jsonb_path_ops);

-- Merge update (safe, no overwrites)
UPDATE np_plugin_config
SET settings = settings || '{"debug": true}'::jsonb
WHERE plugin_name = 'ai';

-- Path access
SELECT settings->'smtp'->>'host' AS smtp_host
FROM np_plugin_config WHERE plugin_name = 'mail';

-- Containment filter (uses GIN index)
SELECT * FROM np_plugin_config WHERE settings @> '{"enabled": true}';

Drizzle Schema Patterns

Relations

import { pgTable, uuid, text, timestamp } from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

export const npUsers = pgTable('np_users', {
  id:              uuid('id').primaryKey().defaultRandom(),
  sourceAccountId: text('source_account_id').notNull().default('primary'),
  email:           text('email').notNull(),
  createdAt:       timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt:       timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  deletedAt:       timestamp('deleted_at', { withTimezone: true }),
})

export const npDocuments = pgTable('np_documents', {
  id:              uuid('id').primaryKey().defaultRandom(),
  sourceAccountId: text('source_account_id').notNull().default('primary'),
  userId:          uuid('user_id').notNull().references(() => npUsers.id),
  title:           text('title').notNull(),
  body:            text('body'),
  createdAt:       timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  deletedAt:       timestamp('deleted_at', { withTimezone: true }),
})

export const usersRelations = relations(npUsers, ({ many }) => ({
  documents: many(npDocuments),
}))

export const documentsRelations = relations(npDocuments, ({ one }) => ({
  user: one(npUsers, {
    fields: [npDocuments.userId],
    references: [npUsers.id],
  }),
}))

Enums

import { pgEnum } from 'drizzle-orm/pg-core'

// Use Postgres enums for fixed value sets
export const planEnum = pgEnum('plan', ['free', 'pro', 'enterprise'])
export const statusEnum = pgEnum('status', ['active', 'suspended', 'deleted'])

export const npTenants = pgTable('np_tenants', {
  id:     uuid('id').primaryKey().defaultRandom(),
  slug:   text('slug').notNull().unique(),
  plan:   planEnum('plan').notNull().default('free'),
  status: statusEnum('status').notNull().default('active'),
})

Hasura Integration

Auto-Tracking

Hasura auto-tracks all np_* tables after nself build. You get full GraphQL CRUD, subscriptions, and aggregate queries immediately.

# After adding a new table:
nself build    # regenerates Hasura metadata
nself start    # applies changes

# Verify table is tracked
nself db hasura metadata status

Computed Fields

-- Function for a computed field on np_users
CREATE FUNCTION np_user_full_name(user_row np_users)
RETURNS TEXT AS $$
  SELECT user_row.first_name || ' ' || user_row.last_name
$$ LANGUAGE sql STABLE;

-- Add to Hasura via nself db hasura metadata import
-- or via the Hasura console

Performance Guidelines

• Partial indexes everywhere — always add WHERE deleted_at IS NULL to indexes on soft-delete tables. This cuts index size by the deletion ratio.
• Composite index column order — highest-cardinality first, then equality columns, then range columns, then order columns.
• JSONB vs columns — use real columns for frequently-queried fields. JSONB is for arbitrary or user-defined config.
• Partition large tables — tables over 50M rows benefit from pg_partman time-based partitioning.
• Run FK audit after every migration — see the PostgreSQL page for the audit query.

Security Guidelines

• Enable RLS on every multi-tenant table — Hasura permissions are a second line of defense, not the first.
• Never store PII in JSONB columns — use real columns so you can selectively encrypt or redact.
• Hash passwords with pgcrypto — never store plaintext. nSelf Auth handles this automatically.
• Prefer ON DELETE RESTRICT over CASCADE — cascade deletes can cause unexpected data loss in deep object graphs.

Related

• Migrations — Drizzle workflow, generate, up/down, conflict resolution
• PostgreSQL — Extensions, RLS, pgvector, indexing
• Multi-Tenancy — Convention Wall, source_account_id vs tenant_id
• Hasura — GraphQL over your schema
• Database Management — Backup, restore, seeds