database-design

Files (1)

• SKILL.md

SKILL.md (first lines)

# Database Design — Founder OS

The database schema is the hardest thing to change after launch. Get it right upfront. Every structural change after users have data costs exponentially more than designing it correctly first.

---

## Core Design Principles

1. **Normalise first, denormalise intentionally** — start with clean relational design, add denormalisation only when you have proven query performance problems
2. **Soft delete by default** — never hard-delete user data without a retention policy
3. **Audit columns on every table** — `created_at`, `updated_at`, `deleted_at`
4. **UUID primary keys** — not auto-increment integers (security + distributed systems)
5. **Foreign key constraints in the database** — don't rely on application logic alone

---

## Base Schema Pattern

```sql
-- Every table inherits this structure
CREATE TABLE [table_name] (
  -- Primary key
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Audit columns (always present)
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at    TIMESTAMPTZ,              -- NULL = active, timestamp = soft deleted

  -- Foreign keys (as needed)
  org_id        UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  created_by    UUID REFERENCES users(id) ON DELETE SET NULL,

  -- Domain columns
  [column]      [type] NOT NULL,
  [column]      [type],                   -- Nullable = truly optional
);

-- Auto-update updated_at on every update
CREATE TRIGGER update_[table]_updated_at
  BEFORE UPDATE ON [table_name]
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();
```

---

## Multi-Tenancy Patterns

Choose based on your security and scale requirements:

### Pattern 1: Shared Database, Shared Schema (RLS)
```sql
-- Every table has org_id
-- Row Level Security filters automatically

ALTER TABLE [table] ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON [table]
  USING (org_id = current_setting('app.current_org_id')::UUID);

-- In application: SET app.current_org_id = '[org_id]';
```
**Best for**: Startups, cost-sensitive, simpler operations

### Pattern 2: Shared Database, Separate Schemas
```sql
-- Each tenant gets their own schema
CREATE SCHEMA tenant_[org_id];
CREATE TABLE tenant_[org_id].users (...);
```
**Best for**: Mid-market, moderate isolation needs

### Pattern 3: Separate Databases
Each tenant gets their own database instance.
**Best for**: Enterprise, high compliance (HIPAA, financial), high-value contracts

---

## Common Schema Patterns

### Users & Authentication
```sql
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         TEXT UNIQUE NOT NULL,
  email_verified BOOLEAN NOT NULL DEFAULT false,
  name          TEXT,
  avatar_url    TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at    TIMESTAMPTZ
);

CREATE TABLE user_sessions (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash    TEXT NOT NULL UNIQUE,   -- Never store raw tokens
  expires_at    TIMESTAMPTZ NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_used_at  TIMESTAMPTZ
);

CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_sessions_expires ON user_sessions(expires_at);
```

### Organizations (Multi-tenant B2B)
```sql
CREATE TABLE organizations (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  slug          TEXT UNIQUE NOT NULL,   -- For URLs: acme-corp
  plan          TEXT NOT NULL DEFAULT 'free',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE org_members (

From The13thNode/VibeCorp_PromptCEO, MIT.

More from The13thNode/VibeCorp_PromptCEO