Reference

Data Models

Bloomberry uses Supabase (PostgreSQL) as its primary database. This page documents the core tables, their fields, and their relationships.

All tables are scoped by user

Every table includes a user_id foreign key referencing the Clerk user ID. Row-level security (RLS) policies ensure users can only access their own data.

writer_sessions

Stores each Writer conversation session with its messages and generated outputs.

writer_sessions schema

"color:#ff7b72">interface WriterSession {
  id: "color:#79c0ff">string              "color:#6e7681">// UUID primary key
  user_id: "color:#79c0ff">string         "color:#6e7681">// Clerk user ID
  title: "color:#79c0ff">string           "color:#6e7681">// Auto-generated session title
  concept: "color:#79c0ff">string         "color:#6e7681">// Original user concept/prompt
  formats: "color:#79c0ff">string[]       "color:#6e7681">// Selected output formats
  style: "color:#79c0ff">string           "color:#6e7681">// Writing style (polished | natural | punchy)
  messages: Message[]     "color:#6e7681">// JSONB: chat message history
  outputs: Record<"color:#79c0ff">string, GeneratedOutput> "color:#6e7681">// JSONB: generated content
  created_at: "color:#79c0ff">string      "color:#6e7681">// ISO timestamp
  updated_at: "color:#79c0ff">string      "color:#6e7681">// ISO timestamp
  is_project: "color:#79c0ff">boolean     "color:#6e7681">// Pinned as a project
}

writer_projects

Pinned sessions promoted to named projects. A project is a writer_session with is_project: true and additional metadata.

writer_projects schema

"color:#ff7b72">interface WriterProject {
  id: "color:#79c0ff">string
  user_id: "color:#79c0ff">string
  session_id: "color:#79c0ff">string      "color:#6e7681">// FK → writer_sessions.id
  name: "color:#79c0ff">string
  description: "color:#79c0ff">string
  tags: "color:#79c0ff">string[]
  created_at: "color:#79c0ff">string
  updated_at: "color:#79c0ff">string
}

scheduled_posts

Scheduled publishing queue. Each row represents a post scheduled for future publishing.

scheduled_posts schema

"color:#ff7b72">interface ScheduledPost {
  id: "color:#79c0ff">string
  user_id: "color:#79c0ff">string
  platform: 'linkedin' | 'twitter' | 'wordpress'
  content: "color:#79c0ff">string         "color:#6e7681">// Post content
  media_url?: "color:#79c0ff">string      "color:#6e7681">// Optional attached media
  scheduled_at: "color:#79c0ff">string    "color:#6e7681">// Target publish time (ISO)
  status: 'pending' | 'published' | 'failed' | 'cancelled'
  platform_post_id?: "color:#79c0ff">string "color:#6e7681">// ID returned by platform after publish
  error?: "color:#79c0ff">string          "color:#6e7681">// Error message "color:#ff7b72">if failed
  connection_id: "color:#79c0ff">string   "color:#6e7681">// FK → social_connections.id
  created_at: "color:#79c0ff">string
}

OAuth connection records for connected publishing platforms.

social_connections schema

"color:#ff7b72">interface SocialConnection {
  id: "color:#79c0ff">string
  user_id: "color:#79c0ff">string
  platform: 'twitter' | 'linkedin'
  access_token: "color:#79c0ff">string    "color:#6e7681">// Encrypted OAuth access token
  refresh_token?: "color:#79c0ff">string  "color:#6e7681">// Encrypted OAuth refresh token
  expires_at?: "color:#79c0ff">string     "color:#6e7681">// Token expiry time
  platform_user_id: "color:#79c0ff">string "color:#6e7681">// User ID on the platform
  platform_username: "color:#79c0ff">string
  platform_display_name: "color:#79c0ff">string
  scopes: "color:#79c0ff">string[]        "color:#6e7681">// Granted OAuth scopes
  is_active: "color:#79c0ff">boolean
  created_at: "color:#79c0ff">string
  updated_at: "color:#79c0ff">string
}

user_knowledge

Knowledge base entries used to ground AI generation in user context.

user_knowledge schema

"color:#ff7b72">interface UserKnowledge {
  id: "color:#79c0ff">string
  user_id: "color:#79c0ff">string
  title: "color:#79c0ff">string
  content: "color:#79c0ff">string         "color:#6e7681">// The knowledge entry text
  category: 'about_me' | 'audience' | 'writing_style'
  embedding?: "color:#79c0ff">number[]    "color:#6e7681">// Vector embedding "color:#ff7b72">for semantic search (pgvector)
  created_at: "color:#79c0ff">string
  updated_at: "color:#79c0ff">string
}

user_brand_kit

Brand identity and voice configuration.

user_brand_kit schema

"color:#ff7b72">interface UserBrandKit {
  id: "color:#79c0ff">string
  user_id: "color:#79c0ff">string
  brand_name?: "color:#79c0ff">string
  tagline?: "color:#79c0ff">string
  logo_url?: "color:#79c0ff">string       "color:#6e7681">// Supabase storage URL
  primary_color?: "color:#79c0ff">string  "color:#6e7681">// Hex code
  secondary_color?: "color:#79c0ff">string
  accent_color?: "color:#79c0ff">string
  voice_prompt?: "color:#79c0ff">string   "color:#6e7681">// Max 500 chars
  created_at: "color:#79c0ff">string
  updated_at: "color:#79c0ff">string
}

user_voice_profiles

Stores voice training samples and analyzed voice fingerprint data.

user_voice_profiles schema

"color:#ff7b72">interface UserVoiceProfile {
  id: "color:#79c0ff">string
  user_id: "color:#79c0ff">string
  samples: VoiceSample[]     "color:#6e7681">// JSONB: array of training samples
  fingerprint?: VoiceFingerprint "color:#6e7681">// JSONB: analyzed voice data
  tuning: VoiceTuning        "color:#6e7681">// JSONB: 9-axis tuning values
  banned_phrases: "color:#79c0ff">string[]   "color:#6e7681">// Custom banned phrases
  analyzed_at?: "color:#79c0ff">string       "color:#6e7681">// Last analysis timestamp
  created_at: "color:#79c0ff">string
  updated_at: "color:#79c0ff">string
}

Relationships

users (Clerk) ──┬── writer_sessions
├── writer_projects (extends writer_sessions)
├── scheduled_posts ──── social_connections
├── social_connections
├── user_knowledge
├── user_brand_kit
└── user_voice_profiles

Edit this page on GitHub