08 — Brand JSON Spec & Database Schemas

08 — Brand JSON Spec & Database Schemas

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

1. De volledige Brand JSON spec (output van de onboarding wizard)
2. Alle database schemas, geconsolideerd uit alle vision documenten

---

Inhoudsopgave

1. Brand JSON Contract
2. Database Schemas — Brand & Tokens
3. Database Schemas — Addons & Content
4. Database Schemas — AI Generatie
5. Database Schemas — Experience & Community
6. Index Overzicht
7. RLS Policies

---

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.

{
  "version": "1.0",
  "created_at": "2026-02-14T12:00:00Z",

  "company": {
    "name": "Bouwbedrijf De Vries",
    "tagline": "Vakmanschap sinds 1985",
    "description": "Renovatie en nieuwbouw voor particulieren in de regio Utrecht",
    "industry": "construction",
    "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,
    "summary": "Professioneel maar benaderbaar. Vakkundig zonder jargon. Betrouwbaar en persoonlijk."
  },

  "visual_identity": {
    "logo_url": "https://r2.beam.app/sites/abc123/brand/logo.svg",
    "colors": {
      "primary": "#1B4D7A",
      "secondary": "#F5F0EB",
      "accent": "#D4883E"
    },
    "font_preference": "classic-serif",
    "mood": "professional-warm"
  },

  "archetype": {
    "gilde": "builder",
    "class": "curator",
    "vertical_pack_id": "aannemer"
  },

  "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",
        "ai_description": "Moderne woonkamer met eiken vloer en grote raampartij"
      }
    ]
  },

  "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"
  },

  "ai_refinement": {
    "completed": true,
    "additional_context": "Focust op duurzame renovaties. Werkt veel met monumentale panden. Klanten waarderen transparante communicatie over planning en kosten."
  }
}

Wat er met uploads gebeurt

1. Teksten (docx, txt, pdf) → opgeslagen in R2 + tekst geëxtraheerd als extracted_text
2. Afbeeldingen → opgeslagen in R2 + AI genereert ai_description voor slimme matching
3. Logo → opgeslagen in R2, URL in de JSON

Data flow na onboarding

POST /api/onboarding/complete  ← Brand JSON + uploads
  ↓
1. brand_profiles aanmaken (onboarding_data + initieel token schema vanuit Style Pack)
2. Uploads verifiëren (R2 URLs)
3. Vertical pack activeren (blueprint uitrollen)
4. Gebruiker redirect naar Beam dashboard

---

2. Database Schemas — Brand & Tokens

brand_profiles

Kolom	Type	Omschrijving
id	uuid, PK
site_id	uuid, FK → sites	Eén brand profile per site
onboarding_data	jsonb	Ruwe onboarding Brand JSON (ongewijzigd)
design_tokens	jsonb	Volledig token schema (output van styling builder)
style_pack_id	text, nullable	Actieve Style Pack ID (‘healer-calm’, etc.)
gilde	text, nullable	Gilde van de site owner
created_at	timestamptz
updated_at	timestamptz

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 '{}',
  style_pack_id 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);
CREATE INDEX idx_brand_profiles_gilde ON brand_profiles(gilde);

ALTER TABLE brand_profiles ENABLE ROW LEVEL SECURITY;
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())
  ));
CREATE POLICY "Public read brand tokens"
  ON brand_profiles FOR SELECT
  USING (true);

token_versions

Kolom	Type	Omschrijving
id	uuid, PK
site_id	uuid, FK → sites
tokens	jsonb	Snapshot van token schema
name	text, nullable	null = auto-save, string = named snapshot
created_at	timestamptz
created_by	uuid, 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())
  ));

Auto-saves worden na 30 dagen opgeruimd. Named snapshots blijven permanent.

style_packs

Kolom	Type	Omschrijving
id	text, PK	’healer-calm’, ‘creator-bold’, etc.
name	text	UI naam
gilde	text, nullable	Gekoppeld gilde
tokens	jsonb	Volledige token set als defaults
seasonal_overrides	jsonb, nullable	Tijdgebonden aanpassingen
is_system	boolean	true = Beam-geleverd, false = user-created
created_at	timestamptz

CREATE TABLE style_packs (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  gilde TEXT,
  tokens JSONB NOT NULL DEFAULT '{}',
  seasonal_overrides JSONB,
  is_system BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_style_packs_gilde ON style_packs(gilde);

---

3. Database Schemas — Addons & Content

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

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

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

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

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

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

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;
-- Security gehandhaafd via applicatielaag op basis van source ownership
CREATE POLICY "Authenticated users manage refs"
  ON content_references FOR ALL
  USING ((SELECT auth.uid()) IS NOT NULL);

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,              -- 'first-blog-post', 'first-booking'
  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())
  ));

---

4. Database Schemas — AI Generatie

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

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

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
  generated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(media_id)
);

CREATE INDEX idx_media_descriptions_media ON media_descriptions(media_id);
-- Toekomst: CREATE INDEX idx_media_descriptions_embedding ON media_descriptions USING ivfflat (embedding);

---

5. Database Schemas — Experience & Community

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

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

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

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

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

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

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,                      -- CF R2 URL
  screenshot_at TIMESTAMPTZ,
  highlights TEXT[] DEFAULT '{}',           -- 3-4 kernwoorden
  niche TEXT,
  active BOOLEAN DEFAULT true,
  quality_score INTEGER,                    -- Intern: site completeness check
  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

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()));

---

6. Index Overzicht

Alle indexes samengevat per categorie:

Brand & Tokens

CREATE INDEX idx_brand_profiles_site ON brand_profiles(site_id);
CREATE INDEX idx_brand_profiles_gilde ON brand_profiles(gilde);
CREATE INDEX idx_token_versions_site ON token_versions(site_id, created_at DESC);
CREATE INDEX idx_style_packs_gilde ON style_packs(gilde);

Addons

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

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

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);

---

7. 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. Zie TECHNICAL_OVERVIEW.md §6 voor de actuele implementatie.