Data & Schemas

Frequency uses LinkML (YAML) as the single source of truth for data models. From one schema definition, we auto-generate TypeScript types, SQL migrations, and database DDL. This eliminates drift between code and database.

The Pipeline

LinkML YAML

schemas/core.yaml

Source of truth

→

Generation Scripts

generate-types.mjs

generate-migration.mjs

→

TypeScript + SQL

generated/core.ts

Don’t edit

File Organization


packages/models/
├── schemas/                         # ✏️ Edit these — LinkML YAML
│   ├── core.yaml                    # Core tenancy models
│   └── .snapshot.json               # Cached enum/table metadata for CLI
├── generated/                       # 🚫 Don't edit — auto-generated
│   └── core.ts                      # TypeScript interfaces & types
├── scripts/                         # Generation tooling
│   ├── generate-types.mjs           # YAML → TypeScript
│   ├── generate-migration.mjs       # YAML → SQL DDL
│   └── linkml-to-sql.mjs           # LinkML → SQL conversion
└── package.json                     # exports: ./schemas/*, ./generated/*

LinkML Schema Structure

Here’s the actual core tenancy schema:


# packages/models/schemas/core.yaml
id: https://frequencyads.com/schemas/core
name: core
prefixes:
  linkml: https://w3id.org/linkml/
 
classes:
  Organization:
    description: Multi-tenant workspace
    attributes:
      id:
        range: string
        identifier: true
        description: UUID primary key
      name:
        range: string
        required: true
      slug:
        range: string
        description: URL-safe unique identifier
      domain:
        range: string
        description: Email domain for auto-join
      logo_url:
        range: uri
      created_at:
        range: datetime
      updated_at:
        range: datetime
 
  UserProfile:
    description: Extends auth.users with profile data
    attributes:
      id:
        range: string
        identifier: true
        description: FK to auth.users
      display_name:
        range: string
      avatar_url:
        range: uri
      default_org_id:
        range: Organization
        description: FK to organizations
 
  Membership:
    description: User-Organization relationship
    attributes:
      id:
        range: string
        identifier: true
      user_id:
        range: UserProfile
      org_id:
        range: Organization
      role:
        range: OrgRole
      status:
        range: MembershipStatus
 
enums:
  OrgRole:
    permissible_values:
      owner: {}
      admin: {}
      member: {}
      viewer: {}
 
  MembershipStatus:
    permissible_values:
      active: {}
      invited: {}
      suspended: {}
      removed: {}

Generated TypeScript

Running the generation scripts produces clean TypeScript:


// packages/models/generated/core.ts (auto-generated — don't edit)
 
export type OrgRole = 'owner' | 'admin' | 'member' | 'viewer';
export type MembershipStatus = 'active' | 'invited' | 'suspended' | 'removed';
 
export interface Organization {
  id: string;
  name: string;
  slug: string;
  domain?: string;
  logo_url?: string;
  created_at?: string;
  updated_at?: string;
}
 
export interface UserProfile {
  id: string;
  display_name?: string;
  avatar_url?: string;
  default_org_id?: string;
  created_at?: string;
  updated_at?: string;
}
 
export interface Membership {
  id: string;
  user_id: string;
  org_id: string;
  role: OrgRole;
  status: MembershipStatus;
  joined_at?: string;
  updated_at?: string;
}

Usage:


import type { Organization, UserProfile, Membership } from '@frequencyads/models';

Generated SQL

The migration scripts produce SQL DDL for Supabase:


-- supabase/migrations/core_tenancy.sql (auto-generated)
 
CREATE TYPE org_role AS ENUM ('owner', 'admin', 'member', 'viewer');
CREATE TYPE membership_status AS ENUM ('active', 'invited', 'suspended', 'removed');
 
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE,
  domain TEXT,
  logo_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
 
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT,
  avatar_url TEXT,
  default_org_id UUID REFERENCES organizations(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);
 
CREATE TABLE memberships (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES user_profiles(id),
  org_id UUID NOT NULL REFERENCES organizations(id),
  role org_role NOT NULL DEFAULT 'member',
  status membership_status NOT NULL DEFAULT 'active',
  joined_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, org_id)
);

Database Features

The generated SQL also includes:

Auto-updating Timestamps


CREATE TRIGGER update_updated_at
  BEFORE UPDATE ON organizations
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Auto-creating User Profiles


-- Trigger on auth.users insert
CREATE TRIGGER handle_new_user
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

Auto-joining Organizations by Email Domain


CREATE TRIGGER handle_auto_join_org
  AFTER INSERT ON user_profiles
  FOR EACH ROW EXECUTE FUNCTION handle_auto_join_org();

Row-Level Security (RLS)


-- Users can view organizations they belong to
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Members can view their organizations"
  ON organizations FOR SELECT USING (
    id IN (SELECT org_id FROM memberships WHERE user_id = auth.uid() AND status = 'active')
  );
 
-- Users can read/update their own profile
-- Admins can manage memberships (owner/admin roles)

Supabase Integration

Browser Client (Client Components)


'use client';
import { createClient } from '@frequencyads/db/client';
 
function MyComponent() {
  const supabase = createClient();
  const { data } = await supabase.from('organizations').select('*');
}

Server Client (Server Components & Route Handlers)


import { createClient } from '@frequencyads/auth/server';
 
async function getData() {
  const supabase = await createClient();
  const { data, error } = await supabase.from('organizations').select('*');
  return data;
}

Multi-Tenancy Pattern

The @frequencyads/db package provides table prefixing for multi-tenancy:


import { prefixTable } from '@frequencyads/db/prefix';
 
// Scope queries to an organization
const tableName = prefixTable('documents', orgId);

Best Practices

Edit LinkML YAML schemas, never the generated TypeScript or SQL
Run generation scripts after every schema change
Review generated SQL before applying migrations — check indexes, constraints, and RLS policies
Use the generated types throughout your application — they’re the contract between your code and database
Add new entities by adding classes to the YAML schema, never by writing SQL manually
Keep the snapshot (.snapshot.json) up to date — it caches metadata for CLI tooling