Skip to content
Beam Beam Docs

02 — Brand JSON Spec & Database Schemas

Status: 🎨 Vision — Nog niet gebouwd. Single source of truth voor het Brand JSON contract en alle toekomstige database schemas.

Dit document is de technische contractlaag voor het Beam platform. Het bevat:

  1. Het Brand JSON contract (output van de onboarding wizard)
  2. Zod validatie schema
  3. API endpoint specificaties
  4. Alle database schemas, geconsolideerd per domein

Inhoudsopgave

Section titled “Inhoudsopgave”
  1. Brand JSON Contract
  2. Zod Validatie Schema
  3. API Endpoints
  4. Versioning
  5. Database Schemas — Brand & Tokens
  6. Database Schemas — Addons & Content
  7. Database Schemas — AI Generatie
  8. Database Schemas — Experience & Community
  9. Index Overzicht
  10. RLS Policies

1. Brand JSON Contract

Section titled “1. Brand JSON Contract”

Het eindresultaat van de onboarding wizard. Dit JSON object wordt via de Beam API ontvangen en opgeslagen in brand_profiles.onboarding_data.

Wizard stappen en veld-mapping: zie Brand Identity — Wizard Stappen. Sfeer-presets: zie Brand Identity — Sfeer-presets. Font-combinaties: zie Brand Identity — Font-combinaties.

Volledig Voorbeeld

Section titled “Volledig Voorbeeld”
{
  "version": "1.0",
  "created_at": "2026-03-20T12:00:00Z",


  "company": {
    "name": "Bouwbedrijf De Vries",
    "tagline": "Vakmanschap sinds 1985",
    "description": "Renovatie en nieuwbouw voor particulieren in de regio Utrecht",
    "industry": "zakelijke-dienstverlening",
    "sub_niche": "aannemer",
    "website_url": null
  },


  "audience": {
    "type": "b2c",
    "description": "Huiseigenaren in de regio Utrecht die willen verbouwen of nieuwbouw overwegen",
    "age_range": { "min": 30, "max": 65 },
    "market": ["regional"]
  },


  "tone_of_voice": {
    "formality": 0.6,
    "seriousness": 0.7,
    "technicality": 0.3,
    "personality": 0.5
  },


  "visual_identity": {
    "logo_url": "https://r2.beam.app/sites/abc123/brand/logo.svg",
    "colors": {
      "primary": "#1B4D7A",
      "secondary": "#F5F0EB",
      "accent": "#D4883E"
    },
    "font_combination": "system-solid",
    "mood_preset": "solide-strak"
  },


  "content": {
    "requested_pages": ["home", "about", "services", "contact", "portfolio"],
    "existing_texts": [
      {
        "filename": "over-ons.docx",
        "storage_url": "https://r2.beam.app/sites/abc123/media/over-ons.docx",
        "extracted_text": "Bouwbedrijf De Vries is opgericht in 1985 door..."
      }
    ],
    "existing_media": [
      {
        "filename": "project-1.jpg",
        "storage_url": "https://r2.beam.app/sites/abc123/media/project-1.jpg"
      }
    ]
  },


  "goals": {
    "primary_goal": "lead_generation",
    "primary_cta": "Vraag een gratis adviesgesprek aan",
    "secondary_cta": "Bekijk onze projecten",
    "contact": {
      "email": "info@devries-bouw.nl",
      "phone": "+31 30 123 4567",
      "address": "Industrieweg 12, Utrecht"
    },
    "social_media": [
      { "platform": "instagram", "url": "https://instagram.com/devries.bouw" },
      { "platform": "linkedin", "url": "https://linkedin.com/company/devries-bouw" }
    ]
  },


  "positioning": {
    "competitors": ["https://www.concurrent-bouw.nl"],
    "differentiator": "Persoonlijke begeleiding van ontwerp tot oplevering, altijd één vast aanspreekpunt",
    "key_message": "Vakmanschap met een persoonlijke aanpak"
  }
}

Minimaal Voorbeeld (alleen verplichte velden)

Section titled “Minimaal Voorbeeld (alleen verplichte velden)”
{
  "version": "1.0",
  "created_at": "2026-03-20T12:00:00Z",
  "company": {
    "name": "Studio Zenna",
    "description": "Yoga en meditatie voor iedereen",
    "industry": "wellness-zorg",
    "sub_niche": "yoga-studio"
  },
  "audience": {
    "type": "b2c",
    "description": "Mensen die rust zoeken in een druk leven",
    "market": ["local"]
  },
  "tone_of_voice": {
    "formality": 0.3,
    "seriousness": 0.3,
    "technicality": 0.1,
    "personality": 0.8
  },
  "content": {
    "requested_pages": ["home", "about", "contact"]
  },
  "goals": {
    "primary_goal": "lead_generation"
  }
}

Velden per Wizard Stap

Section titled “Velden per Wizard Stap”
StapBrand JSON veldVerplicht
1 — De Basiscompany.*name, description, industry, sub_niche
2 — Doelgroepaudience.*type, description, market
3 — Tone of Voicetone_of_voice.*Alle 4 sliders (defaults: 0.5)
4 — Visuele Identiteitvisual_identity.*Geen (alles optioneel)
5 — Content & Mediacontent.*requested_pages (min 1)
6 — Doelen & Conversiegoals.*primary_goal
7 — Concurrentiepositioning.*Geen (hele stap optioneel)

Wat er met uploads gebeurt

Section titled “Wat er met uploads gebeurt”
TypeOpslagVerwerking
Teksten (docx, txt, pdf)R2: sites/{site_id}/media/Tekst geëxtraheerd als extracted_text
Afbeeldingen (jpg, png, webp)R2: sites/{site_id}/media/Thumbnail gegenereerd, toegevoegd aan media library
Logo (svg, png)R2: sites/{site_id}/brand/URL opgeslagen in visual_identity.logo_url

2. Zod Validatie Schema

Section titled “2. Zod Validatie Schema”

Het Brand JSON wordt gevalideerd bij ontvangst via de API. Dit schema is de single source of truth voor wat geldig is.

import { z } from 'zod'


// Herbruikbare types
const hexColor = z.string().regex(/^#[0-9A-Fa-f]{6}$/)
const slider = z.number().min(0).max(1)
const url = z.string().url()


// Branche slugs uit de taxonomie (zie 01-brand-identity §3)
const industrySlug = z.enum([
  'wellness-zorg',
  'creatieve-industrie',
  'horeca-gastvrijheid',
  'retail-ecommerce',
  'zakelijke-dienstverlening',
  'onderwijs-training',
  'tech-software',
  'non-profit',
  'overig',
])


// Sfeer-preset IDs (zie 01-brand-identity §7)
const moodPresetId = z.enum([
  'warm-aards',
  'bold-expressief',
  'zakelijk-betrouwbaar',
  'verfijnd-diep',
  'gastvrij-sfeervol',
  'solide-strak',
])


// Font-combinatie IDs (zie 01-brand-identity §4)
const fontCombinationId = z.enum([
  'forest-calm',
  'botanical',
  'statement',
  'creative-edge',
  'corporate-clean',
  'trusted-pro',
  'editorial',
  'academic',
  'bistro-charm',
  'cozy-table',
  'system-solid',
  'industrial',
])


const pageSlug = z.enum([
  'home', 'about', 'services', 'contact',
  'blog', 'portfolio', 'pricing', 'faq',
])


const primaryGoal = z.enum([
  'lead_generation',
  'informing',
  'selling',
  'portfolio',
  'community',
])


const audienceType = z.enum(['b2b', 'b2c', 'both'])


const market = z.enum(['local', 'regional', 'national', 'international'])


const socialPlatform = z.enum([
  'instagram', 'linkedin', 'facebook', 'twitter',
  'tiktok', 'youtube', 'pinterest', 'other',
])


// ─── Sectie schemas ───


const companySchema = z.object({
  name: z.string().min(2).max(100),
  tagline: z.string().max(150).optional(),
  description: z.string().min(20).max(300),
  industry: industrySlug,
  sub_niche: z.string().min(1).max(100),
  website_url: url.nullable().optional(),
})


const audienceSchema = z.object({
  type: audienceType,
  description: z.string().min(10).max(500),
  age_range: z.object({
    min: z.number().int().min(16).max(80),
    max: z.number().int().min(16).max(80),
  }).optional(),
  market: z.array(market).min(1),
})


const toneOfVoiceSchema = z.object({
  formality: slider,
  seriousness: slider,
  technicality: slider,
  personality: slider,
})


const visualIdentitySchema = z.object({
  logo_url: url.nullable().optional(),
  colors: z.object({
    primary: hexColor,
    secondary: hexColor,
    accent: hexColor,
  }).partial().optional(),
  font_combination: fontCombinationId.optional(),
  mood_preset: moodPresetId.optional(),
}).optional()


const existingTextSchema = z.object({
  filename: z.string(),
  storage_url: url,
  extracted_text: z.string().optional(),
})


const existingMediaSchema = z.object({
  filename: z.string(),
  storage_url: url,
})


const contentSchema = z.object({
  requested_pages: z.array(pageSlug).min(1),
  existing_texts: z.array(existingTextSchema).max(10).optional(),
  existing_media: z.array(existingMediaSchema).max(20).optional(),
})


const socialMediaSchema = z.object({
  platform: socialPlatform,
  url: url,
})


const goalsSchema = z.object({
  primary_goal: primaryGoal,
  primary_cta: z.string().max(60).optional(),
  secondary_cta: z.string().max(60).optional(),
  contact: z.object({
    email: z.string().email().optional(),
    phone: z.string().max(20).optional(),
    address: z.string().max(200).optional(),
  }).optional(),
  social_media: z.array(socialMediaSchema).max(6).optional(),
})


const positioningSchema = z.object({
  competitors: z.array(z.string().max(200)).max(3).optional(),
  differentiator: z.string().max(300).nullable().optional(),
  key_message: z.string().max(100).nullable().optional(),
}).optional()


// ─── Volledig Brand JSON schema ───


export const brandJsonSchema = z.object({
  version: z.literal('1.0'),
  created_at: z.string().datetime(),


  company: companySchema,
  audience: audienceSchema,
  tone_of_voice: toneOfVoiceSchema,
  visual_identity: visualIdentitySchema,
  content: contentSchema,
  goals: goalsSchema,
  positioning: positioningSchema,
})


export type BrandJson = z.infer<typeof brandJsonSchema>

Validatie Regels

Section titled “Validatie Regels”
RegelImplementatie
age_range.min < age_range.maxZod .refine() op audienceSchema
Kleuren: max 3Structureel afgedwongen (3 velden)
sub_niche moet bij industry passenApplicatielaag — niet in Zod (taxonomie kan veranderen)
Uploads: URL moet R2 prefix hebbenApplicatielaag — niet in Zod

3. API Endpoints

Section titled “3. API Endpoints”

POST /api/onboarding/complete

Section titled “POST /api/onboarding/complete”

Wizard afronden en site aanmaken.

Request:

{
  brand_json: BrandJson  // Gevalideerd tegen brandJsonSchema
}

Response (201):

{
  "site_id": "uuid",
  "brand_profile_id": "uuid",
  "redirect_url": "/sites/{site_id}/brand"
}

Errors:

StatusCodeBeschrijving
400VALIDATION_ERRORBrand JSON faalt Zod validatie. Response bevat issues[]
401UNAUTHORIZEDGeen geldig auth token
409SITE_LIMIT_REACHEDGebruiker heeft max aantal sites bereikt

Flow:

  1. Valideer Brand JSON tegen brandJsonSchema
  2. Maak site record aan
  3. Maak brand_profiles record aan (onboarding_data = Brand JSON, design_tokens = sfeer-preset tokens)
  4. Verifieer upload URLs (moeten R2 prefix matchen)
  5. Koppel uploads aan site media library
  6. Return site ID + redirect URL

GET /api/sites/:site_id/brand

Section titled “GET /api/sites/:site_id/brand”

Brand profile ophalen (inclusief onboarding data en huidige tokens).

Response (200):

{
  "id": "uuid",
  "site_id": "uuid",
  "onboarding_data": { "...Brand JSON..." },
  "design_tokens": { "...resolved token schema..." },
  "mood_preset": "solide-strak",
  "created_at": "2026-03-20T12:00:00Z",
  "updated_at": "2026-03-20T14:30:00Z"
}

Errors:

StatusCodeBeschrijving
401UNAUTHORIZEDNiet ingelogd
403FORBIDDENGeen toegang tot deze site
404NOT_FOUNDSite of brand profile bestaat niet

PUT /api/sites/:site_id/brand

Section titled “PUT /api/sites/:site_id/brand”

Brand profile bijwerken (vanuit de Styling Builder).

Request:

{
  design_tokens?: object    // Volledig token schema (overschrijft)
  mood_preset?: string      // Sfeer-preset wisselen
}

Response (200):

{
  "id": "uuid",
  "design_tokens": { "..." },
  "mood_preset": "warm-aards",
  "updated_at": "2026-03-20T15:00:00Z"
}

Side effects:

  • Maakt automatisch een token_versions snapshot aan bij elke save
  • Triggert token cache invalidatie voor de publieke site

GET /api/sites/:site_id/tokens

Section titled “GET /api/sites/:site_id/tokens”

Design tokens ophalen in W3C formaat. Read-only endpoint, ook beschikbaar zonder auth voor de publieke site.

Response (200):

{
  "brand": { "name": "...", "logo": { "..." } },
  "tokens": {
    "colors": { "..." },
    "typography": { "..." },
    "shape": { "..." }
  },
  "elements": { "..." },
  "meta": { "version": "2.0", "..." }
}

Cache: Cache-Control: public, max-age=300 (5 minuten). Geïnvalideerd bij PUT /brand.

GET /api/sites/:site_id/tokens/css

Section titled “GET /api/sites/:site_id/tokens/css”

Design tokens als CSS variables. Voor embedden in de publieke site <head>.

Response (200, Content-Type: text/css):

:root {
  --color-primary: #1B4D7A;
  --color-primary-50: #E8F0F8;
  --color-primary-100: #C5D9EB;
  /* ... generated shades ... */
  --color-secondary: #F5F0EB;
  --color-accent: #D4883E;
  --color-background: #FFFFFF;
  --color-surface: #F8F9FA;
  --color-text: #1A1A2E;
  --color-text-muted: #6B7280;
  --color-border: #DEE2E6;
  --font-heading: 'Roboto Slab', serif;
  --font-body: 'Roboto', sans-serif;
  --radius-sm: 0px;
  --radius-base: 0px;
  --radius-lg: 0px;
  --spacing-base: 4px;
  --shadow-sm: 0 1px 2px rgba(0,0,0,0.05);
  --shadow-md: 0 4px 6px rgba(0,0,0,0.07);
}

Cache: Cache-Control: public, max-age=300.

GET /api/mood-presets

Section titled “GET /api/mood-presets”

Alle beschikbare sfeer-presets ophalen. Publiek endpoint (geen auth nodig).

Response (200):

{
  "presets": [
    {
      "id": "warm-aards",
      "name": "Warm & Aards",
      "description": "Rustig, aards, ademruimte",
      "colors": {
        "primary": "#87A96B",
        "secondary": "#F5F0EB",
        "accent": "#C4956A"
      },
      "font_combination": "forest-calm",
      "radius": "large",
      "spacing": "spacious",
      "shadows": "subtle"
    },
    { "..." }
  ]
}

Error Response Formaat

Section titled “Error Response Formaat”

Alle API errors volgen hetzelfde formaat:

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Brand JSON validatie gefaald",
    "issues": [
      {
        "path": "company.name",
        "message": "Minimaal 2 tekens vereist"
      }
    ]
  }
}

Regel: error.message is altijd een generieke tekst — geen interne details, stack traces of database errors.

4. Versioning

Section titled “4. Versioning”

Brand JSON Versioning

Section titled “Brand JSON Versioning”

Het version veld in de Brand JSON bepaalt welk schema geldig is.

VersieStatusWijzigingen
1.0ActiefInitiële versie zonder guilds
1.1Gepland+ archetype sectie (guild/class) wanneer guilds gelanceerd worden
2.0Toekomst+ ai_refinement sectie (AI verfijningsfase)

Migratiebeleid

Section titled “Migratiebeleid”
  • Minor versies (1.0 → 1.1): backward compatible. Nieuwe velden zijn optioneel. Oude clients blijven werken.
  • Major versies (1.x → 2.0): breaking changes. Migratiescript nodig. Oude Brand JSONs worden bij eerste load gemigreerd.
  • Opgeslagen data: brand_profiles.onboarding_data bevat altijd de originele Brand JSON. Migratie wijzigt dit niet — alleen de interpretatie verandert.

Token Schema Versioning

Section titled “Token Schema Versioning”

Design tokens hebben een eigen versie in meta.version. Token versies zijn onafhankelijk van Brand JSON versies.

  • token_versions tabel slaat snapshots op bij elke save
  • Auto-saves: 30 dagen retentie, daarna opgeruimd
  • Named snapshots: permanent

5. Database Schemas — Brand & Tokens

Section titled “5. Database Schemas — Brand & Tokens”

brand_profiles

Section titled “brand_profiles”

De kern van het brand systeem. Eén record per site.

KolomTypeOmschrijving
iduuid, PK
site_iduuid, FK → sites, UNIQUEEén brand profile per site
onboarding_datajsonb, nullableRuwe onboarding Brand JSON (ongewijzigd origineel)
design_tokensjsonb, NOT NULLVolledig resolved token schema (output Styling Builder)
mood_presettext, nullableActieve sfeer-preset ID ('warm-aards', 'solide-strak', etc.)
gildetext, nullableToekomst: guild ID wanneer guilds gelanceerd worden
created_attimestamptz
updated_attimestamptz
CREATE TABLE brand_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  onboarding_data JSONB,
  design_tokens JSONB NOT NULL DEFAULT '{}',
  mood_preset TEXT,
  gilde TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(site_id)
);


CREATE INDEX idx_brand_profiles_site ON brand_profiles(site_id);


ALTER TABLE brand_profiles ENABLE ROW LEVEL SECURITY;


-- Site owner kan alles doen met het brand profile
CREATE POLICY "Team members manage brand profile"
  ON brand_profiles FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));


-- Publieke site leest tokens (voor CSS rendering)
CREATE POLICY "Public read brand tokens"
  ON brand_profiles FOR SELECT
  USING (true);

Noot: design_tokens bevat het volledig resolved token schema — niet de Brand JSON. De Brand JSON staat in onboarding_data als ongewijzigd origineel. De Styling Builder leest design_tokens, wijzigt het, en slaat het terug op.

token_versions

Section titled “token_versions”

Snapshot history van token wijzigingen.

KolomTypeOmschrijving
iduuid, PK
site_iduuid, FK → sites
tokensjsonb, NOT NULLSnapshot van het volledige token schema
nametext, nullablenull = auto-save, string = named snapshot (“v1 Launch”)
created_attimestamptz
created_byuuid, FK → users
CREATE TABLE token_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  tokens JSONB NOT NULL,
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  created_by UUID REFERENCES auth.users(id)
);


CREATE INDEX idx_token_versions_site ON token_versions(site_id, created_at DESC);


ALTER TABLE token_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members manage token versions"
  ON token_versions FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));

Retentie: Auto-saves (name IS NULL) worden na 30 dagen opgeruimd via cron. Named snapshots blijven permanent.

mood_presets

Section titled “mood_presets”

Sfeer-presets als seed data. Bevat de 6 proto-guild presets + eventuele custom presets.

KolomTypeOmschrijving
idtext, PK'warm-aards', 'solide-strak', etc.
nametext, NOT NULLUI naam: “Warm & Aards”
descriptiontextKorte beschrijving voor preset-picker
tokensjsonb, NOT NULLVolledige token set als defaults
font_combinationtextDefault font-combinatie ID
radiustextDefault radius preset
spacingtextDefault spacing preset
shadowstextDefault shadow preset
colorsjsonb, NOT NULL{ primary, secondary, accent }
is_systemboolean, NOT NULLtrue = Beam-geleverd, false = user-created
created_attimestamptz
CREATE TABLE mood_presets (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  tokens JSONB NOT NULL DEFAULT '{}',
  font_combination TEXT,
  radius TEXT CHECK (radius IN ('none', 'small', 'medium', 'large', 'pill')),
  spacing TEXT CHECK (spacing IN ('compact', 'comfortable', 'spacious')),
  shadows TEXT CHECK (shadows IN ('none', 'subtle', 'medium', 'prominent')),
  colors JSONB NOT NULL DEFAULT '{}',
  is_system BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);


-- Seed data: de 6 proto-guild presets
-- Zie 01-brand-identity §7 voor de volledige definitie van elke preset

Toekomst: Wanneer guilds worden gelanceerd, krijgt deze tabel een gilde kolom die de mapping legt:

  • warm-aards → healer
  • bold-expressief → creator
  • zakelijk-betrouwbaar → merchant
  • verfijnd-diep → scholar
  • gastvrij-sfeervol → host
  • solide-strak → builder

6. Database Schemas — Addons & Content

Section titled “6. Database Schemas — Addons & Content”

Status: 🔮 Toekomst — afhankelijk van Expansion Pack Systeem (05). Schemas hier zijn indicatief en worden uitgewerkt wanneer het addon-systeem concreet wordt.

addons

Section titled “addons”
CREATE TABLE addons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  addon_type_id TEXT NOT NULL,
  specialization TEXT,
  config JSONB NOT NULL DEFAULT '{}',
  enabled BOOLEAN NOT NULL DEFAULT true,
  installed_via TEXT,                       -- 'manual' | 'vertical-pack:yoga-studio'
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(site_id, addon_type_id)
);


CREATE INDEX idx_addons_site ON addons(site_id);
CREATE INDEX idx_addons_type ON addons(site_id, addon_type_id);


ALTER TABLE addons ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members manage addons"
  ON addons FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));

vertical_packs

Section titled “vertical_packs”
CREATE TABLE vertical_packs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  pack_type_id TEXT NOT NULL,
  config JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(site_id, pack_type_id)
);

addon_collections

Section titled “addon_collections”
CREATE TABLE addon_collections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  addon_id UUID NOT NULL REFERENCES addons(id) ON DELETE CASCADE,
  collection_key TEXT NOT NULL,             -- 'posts', 'products'
  config JSONB NOT NULL DEFAULT '{}',
  UNIQUE(addon_id, collection_key)
);

addon_items

Section titled “addon_items”
CREATE TABLE addon_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  collection_id UUID NOT NULL REFERENCES addon_collections(id) ON DELETE CASCADE,
  site_id UUID NOT NULL REFERENCES sites(id),
  title TEXT NOT NULL,
  slug TEXT,
  blocks JSONB NOT NULL DEFAULT '[]',
  metadata JSONB NOT NULL DEFAULT '{}',
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'published', 'scheduled')),
  sort_order INTEGER DEFAULT 0,
  published_at TIMESTAMPTZ,
  created_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(collection_id, slug)
);


CREATE INDEX idx_addon_items_collection ON addon_items(collection_id);
CREATE INDEX idx_addon_items_site ON addon_items(site_id);
CREATE INDEX idx_addon_items_status ON addon_items(collection_id, status);
CREATE INDEX idx_addon_items_published ON addon_items(collection_id, status, published_at DESC);
CREATE INDEX idx_addon_items_slug ON addon_items(collection_id, slug);
CREATE INDEX idx_addon_items_blocks ON addon_items USING GIN (blocks jsonb_path_ops);
CREATE INDEX idx_addon_items_metadata ON addon_items USING GIN (metadata jsonb_path_ops);


ALTER TABLE addon_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members manage items"
  ON addon_items FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));
CREATE POLICY "Published items are public"
  ON addon_items FOR SELECT
  USING (status = 'published' AND (published_at IS NULL OR published_at <= now()));

addon_archive_pages

Section titled “addon_archive_pages”
CREATE TABLE addon_archive_pages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  addon_id UUID NOT NULL REFERENCES addons(id) ON DELETE CASCADE,
  site_id UUID NOT NULL REFERENCES sites(id),
  route_pattern TEXT NOT NULL,
  blocks JSONB NOT NULL DEFAULT '[]',
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'published')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(addon_id, route_pattern)
);


CREATE INDEX idx_addon_archive_pages_addon ON addon_archive_pages(addon_id);

addon_taxonomies + Junction

Section titled “addon_taxonomies + Junction”
CREATE TABLE addon_taxonomies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  collection_id UUID NOT NULL REFERENCES addon_collections(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  slug TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('category', 'tag')),
  parent_id UUID REFERENCES addon_taxonomies(id),
  sort_order INTEGER DEFAULT 0,
  UNIQUE(collection_id, slug, type)
);


CREATE TABLE addon_item_taxonomies (
  item_id UUID NOT NULL REFERENCES addon_items(id) ON DELETE CASCADE,
  taxonomy_id UUID NOT NULL REFERENCES addon_taxonomies(id) ON DELETE CASCADE,
  PRIMARY KEY (item_id, taxonomy_id)
);


CREATE INDEX idx_addon_taxonomies_collection ON addon_taxonomies(collection_id);
CREATE INDEX idx_addon_taxonomies_parent ON addon_taxonomies(parent_id);


ALTER TABLE addon_taxonomies ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members manage taxonomies"
  ON addon_taxonomies FOR ALL
  USING (collection_id IN (
    SELECT ac.id FROM addon_collections ac
    JOIN addons a ON a.id = ac.addon_id
    WHERE a.site_id IN (
      SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
    )
  ));
CREATE POLICY "Public taxonomy read"
  ON addon_taxonomies FOR SELECT
  USING (true);

content_references

Section titled “content_references”
CREATE TABLE content_references (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source_type TEXT NOT NULL,                -- 'page', 'addon_item'
  source_id UUID NOT NULL,
  target_type TEXT NOT NULL,
  target_id UUID NOT NULL,
  relation TEXT NOT NULL,                   -- 'related', 'embedded', 'linked'
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(source_type, source_id, target_type, target_id, relation)
);


CREATE INDEX idx_content_refs_source ON content_references(source_type, source_id);
CREATE INDEX idx_content_refs_target ON content_references(target_type, target_id);


ALTER TABLE content_references ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Authenticated users manage refs"
  ON content_references FOR ALL
  USING ((SELECT auth.uid()) IS NOT NULL);

site_milestones

Section titled “site_milestones”
CREATE TABLE site_milestones (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  milestone_key TEXT NOT NULL,
  completed_at TIMESTAMPTZ,
  UNIQUE(site_id, milestone_key)
);


CREATE INDEX idx_site_milestones_site ON site_milestones(site_id);


ALTER TABLE site_milestones ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members manage milestones"
  ON site_milestones FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));

7. Database Schemas — AI Generatie

Section titled “7. Database Schemas — AI Generatie”

Status: 🔮 Toekomst — afhankelijk van AI Content Generatie (07). Schemas hier zijn indicatief.

ai_generations

Section titled “ai_generations”
CREATE TABLE ai_generations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  page_id UUID REFERENCES pages(id) ON DELETE SET NULL,
  mode TEXT NOT NULL CHECK (mode IN ('quick', 'conversational')),
  user_prompt TEXT NOT NULL,
  enriched_prompt TEXT,
  ai_response JSONB,
  model_used TEXT,
  tokens_used INTEGER,
  tone_override JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);


CREATE INDEX idx_ai_generations_site ON ai_generations(site_id, created_at DESC);


ALTER TABLE ai_generations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members read own generations"
  ON ai_generations FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));

page_content_summaries

Section titled “page_content_summaries”
CREATE TABLE page_content_summaries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  page_id UUID NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
  summary TEXT NOT NULL,
  key_terms TEXT[] DEFAULT '{}',
  ctas_used TEXT[] DEFAULT '{}',
  generated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(page_id)
);

media_descriptions

Section titled “media_descriptions”
CREATE TABLE media_descriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  media_id UUID NOT NULL REFERENCES media(id) ON DELETE CASCADE,
  description TEXT NOT NULL,
  tags TEXT[] DEFAULT '{}',
  embedding VECTOR(1536),                   -- pgvector: semantic search (toekomst)
  generated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(media_id)
);


CREATE INDEX idx_media_descriptions_media ON media_descriptions(media_id);

8. Database Schemas — Experience & Community

Section titled “8. Database Schemas — Experience & Community”

Status: 🔮 Toekomst — afhankelijk van Experience & Community (08). Schemas hier zijn indicatief.

user_achievements

Section titled “user_achievements”
CREATE TABLE user_achievements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  achievement_id TEXT NOT NULL,
  unlocked_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, achievement_id)
);


CREATE INDEX idx_user_achievements_user ON user_achievements(user_id);


ALTER TABLE user_achievements ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own achievements"
  ON user_achievements FOR ALL
  USING (user_id = (SELECT auth.uid()));
CREATE POLICY "Achievements public read"
  ON user_achievements FOR SELECT
  USING (true);

site_journey

Section titled “site_journey”
CREATE TABLE site_journey (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  chapter INTEGER NOT NULL DEFAULT 1,
  milestones_completed JSONB NOT NULL DEFAULT '[]',
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(site_id)
);


ALTER TABLE site_journey ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Team members manage journey"
  ON site_journey FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));

beam_events

Section titled “beam_events”
CREATE TABLE beam_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id),
  site_id UUID NOT NULL REFERENCES sites(id),
  event_type TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);


CREATE INDEX idx_beam_events_user ON beam_events(user_id, event_type);
CREATE INDEX idx_beam_events_site ON beam_events(site_id, event_type);
CREATE INDEX idx_beam_events_time ON beam_events(created_at DESC);

beam_profiles

Section titled “beam_profiles”
CREATE TABLE beam_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT,
  bio TEXT,
  location_city TEXT,
  location_country TEXT DEFAULT 'NL',
  gilde TEXT,
  class TEXT,
  tags TEXT[] DEFAULT '{}',
  open_for_collaboration BOOLEAN DEFAULT false,
  seeking TEXT[] DEFAULT '{}',
  directory_visible BOOLEAN DEFAULT false,
  profile_visible BOOLEAN DEFAULT true,
  show_achievements BOOLEAN DEFAULT true,
  show_connections BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);


CREATE INDEX idx_beam_profiles_gilde ON beam_profiles(gilde);
CREATE INDEX idx_beam_profiles_location ON beam_profiles(location_city);
CREATE INDEX idx_beam_profiles_directory ON beam_profiles(directory_visible)
  WHERE directory_visible = true;
CREATE INDEX idx_beam_profiles_collab ON beam_profiles(open_for_collaboration)
  WHERE open_for_collaboration = true;
CREATE INDEX idx_beam_profiles_tags ON beam_profiles USING GIN (tags);


ALTER TABLE beam_profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own profile"
  ON beam_profiles FOR ALL
  USING (id = (SELECT auth.uid()));
CREATE POLICY "Profiles visible to authenticated"
  ON beam_profiles FOR SELECT
  USING (profile_visible = true AND (SELECT auth.uid()) IS NOT NULL);

connections

Section titled “connections”
CREATE TABLE connections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  requester_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  receiver_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'accepted', 'declined')),
  message TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  responded_at TIMESTAMPTZ,
  UNIQUE(requester_id, receiver_id),
  CHECK (requester_id != receiver_id)
);


CREATE INDEX idx_connections_requester ON connections(requester_id);
CREATE INDEX idx_connections_receiver ON connections(receiver_id);
CREATE INDEX idx_connections_status ON connections(status);


ALTER TABLE connections ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own connections"
  ON connections FOR ALL
  USING (
    requester_id = (SELECT auth.uid()) OR
    receiver_id = (SELECT auth.uid())
  );

messages

Section titled “messages”
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sender_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  receiver_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  content TEXT NOT NULL CHECK (char_length(content) <= 2000),
  read_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);


CREATE INDEX idx_messages_receiver ON messages(receiver_id, read_at)
  WHERE read_at IS NULL;
CREATE INDEX idx_messages_conversation ON messages(
  LEAST(sender_id, receiver_id),
  GREATEST(sender_id, receiver_id),
  created_at DESC
);


ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users see own messages"
  ON messages FOR SELECT
  USING (
    sender_id = (SELECT auth.uid()) OR
    receiver_id = (SELECT auth.uid())
  );
CREATE POLICY "Users send messages to connections"
  ON messages FOR INSERT
  WITH CHECK (
    sender_id = (SELECT auth.uid()) AND
    EXISTS (
      SELECT 1 FROM connections
      WHERE status = 'accepted'
      AND (
        (requester_id = (SELECT auth.uid()) AND receiver_id = messages.receiver_id) OR
        (receiver_id = (SELECT auth.uid()) AND requester_id = messages.receiver_id)
      )
    )
  );

directory_listings

Section titled “directory_listings”
CREATE TABLE directory_listings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  screenshot_url TEXT,
  screenshot_at TIMESTAMPTZ,
  highlights TEXT[] DEFAULT '{}',
  niche TEXT,
  active BOOLEAN DEFAULT true,
  quality_score INTEGER,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(site_id)
);


CREATE INDEX idx_directory_listings_active ON directory_listings(active)
  WHERE active = true;
CREATE INDEX idx_directory_listings_niche ON directory_listings(niche);


ALTER TABLE directory_listings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Directory listings public read"
  ON directory_listings FOR SELECT
  USING (active = true);
CREATE POLICY "Site owner manages listing"
  ON directory_listings FOR ALL
  USING (site_id IN (
    SELECT id FROM sites WHERE owner_id = (SELECT auth.uid())
  ));

user_preferences

Section titled “user_preferences”
CREATE TABLE user_preferences (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  sound_enabled BOOLEAN DEFAULT true,
  sound_volume INTEGER DEFAULT 70 CHECK (sound_volume BETWEEN 0 AND 100),
  music_enabled BOOLEAN DEFAULT false,
  music_volume INTEGER DEFAULT 40 CHECK (music_volume BETWEEN 0 AND 100),
  sound_profile TEXT DEFAULT 'default',
  cursor_style TEXT DEFAULT 'beam' CHECK (cursor_style IN ('beam', 'gilde', 'system')),
  reduced_motion BOOLEAN DEFAULT false,
  updated_at TIMESTAMPTZ DEFAULT now()
);


ALTER TABLE user_preferences ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own preferences"
  ON user_preferences FOR ALL
  USING (user_id = (SELECT auth.uid()));

9. Index Overzicht

Section titled “9. Index Overzicht”

Brand & Tokens

Section titled “Brand & Tokens”
CREATE INDEX idx_brand_profiles_site ON brand_profiles(site_id);
CREATE INDEX idx_token_versions_site ON token_versions(site_id, created_at DESC);

Addons (toekomst)

Section titled “Addons (toekomst)”
CREATE INDEX idx_addons_site ON addons(site_id);
CREATE INDEX idx_addons_type ON addons(site_id, addon_type_id);
CREATE INDEX idx_addon_items_collection ON addon_items(collection_id);
CREATE INDEX idx_addon_items_site ON addon_items(site_id);
CREATE INDEX idx_addon_items_status ON addon_items(collection_id, status);
CREATE INDEX idx_addon_items_published ON addon_items(collection_id, status, published_at DESC);
CREATE INDEX idx_addon_items_slug ON addon_items(collection_id, slug);
CREATE INDEX idx_addon_items_blocks ON addon_items USING GIN (blocks jsonb_path_ops);
CREATE INDEX idx_addon_items_metadata ON addon_items USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_addon_archive_pages_addon ON addon_archive_pages(addon_id);
CREATE INDEX idx_addon_taxonomies_collection ON addon_taxonomies(collection_id);
CREATE INDEX idx_addon_taxonomies_parent ON addon_taxonomies(parent_id);
CREATE INDEX idx_content_refs_source ON content_references(source_type, source_id);
CREATE INDEX idx_content_refs_target ON content_references(target_type, target_id);
CREATE INDEX idx_site_milestones_site ON site_milestones(site_id);

AI Generatie (toekomst)

Section titled “AI Generatie (toekomst)”
CREATE INDEX idx_ai_generations_site ON ai_generations(site_id, created_at DESC);
CREATE INDEX idx_media_descriptions_media ON media_descriptions(media_id);

Experience & Community (toekomst)

Section titled “Experience & Community (toekomst)”
CREATE INDEX idx_user_achievements_user ON user_achievements(user_id);
CREATE INDEX idx_beam_events_user ON beam_events(user_id, event_type);
CREATE INDEX idx_beam_events_site ON beam_events(site_id, event_type);
CREATE INDEX idx_beam_events_time ON beam_events(created_at DESC);
CREATE INDEX idx_beam_profiles_gilde ON beam_profiles(gilde);
CREATE INDEX idx_beam_profiles_location ON beam_profiles(location_city);
CREATE INDEX idx_beam_profiles_directory ON beam_profiles(directory_visible) WHERE directory_visible = true;
CREATE INDEX idx_beam_profiles_collab ON beam_profiles(open_for_collaboration) WHERE open_for_collaboration = true;
CREATE INDEX idx_beam_profiles_tags ON beam_profiles USING GIN (tags);
CREATE INDEX idx_connections_requester ON connections(requester_id);
CREATE INDEX idx_connections_receiver ON connections(receiver_id);
CREATE INDEX idx_connections_status ON connections(status);
CREATE INDEX idx_messages_receiver ON messages(receiver_id, read_at) WHERE read_at IS NULL;
CREATE INDEX idx_messages_conversation ON messages(LEAST(sender_id, receiver_id), GREATEST(sender_id, receiver_id), created_at DESC);
CREATE INDEX idx_directory_listings_active ON directory_listings(active) WHERE active = true;
CREATE INDEX idx_directory_listings_niche ON directory_listings(niche);

10. RLS Policies

Section titled “10. RLS Policies”

Alle RLS policies volgen de Beam security standaard:

  • (SELECT auth.uid()) — performance pattern (subquery cached per statement)
  • Team access via sites WHERE owner_id = (SELECT auth.uid()) — direct ownership check
  • Public content via status = 'published' guard

Noot: get_team_owner_id() is een Supabase database function die team member access afhandelt. Gebruik in RLS policies volgt het patroon sites WHERE owner_id = (SELECT auth.uid()) voor directe eigenaarsvragen en de helper functie voor team-scoped queries.