schema-reference

Supabase Schema Reference

This document provides a complete reference of available tables across Supabase projects used for client context retrieval and internal operations.

---

Project Overview

Project	ID	Purpose	Table Count
Internal AI Core	kxzonslnqjsceiublxkf	Client registry, team directory, agents, service offerings	20+ tables
Slack Messages	oqtkgsndvitzyfzwcdoz	Slack channel history, content blocks, embeddings	60+ tables
Zoom Transcripts	viqeppmsqvwpslpvttkk	Meeting transcripts, summaries, client embeddings	40+ tables

---

Internal AI Core Project (kxzonslnqjsceiublxkf)

This is the central operational database that ties everything together. It contains the client registry, team directory, and agent configurations.

Core Tables

Clients Table

Table: clients (30 rows)

Column	Type	Description
id	uuid	Primary key
name	text	Client name
slack_channel_id	text	Client Slack channel ID
slack_messages_table	text	Table name in Slack project
slack_embeddings_table	text	Embeddings table in Slack project
zoom_embeddings_table	text	Embeddings table in Zoom project
zoom_query	text	Query filter for Zoom meetings
meeting_tag_prefix	text	Prefix for meeting folder names
linear_team_id	text	Associated Linear team
is_external	boolean	External client flag (default: true)
active_status	boolean	Active engagement flag
automations_active	boolean	Automations enabled
aliases	text[]	Alternative names for matching
description	text	Client description
tags	text[]	Client tags

Team Table

Table: team (51 rows)

Column	Type	Description
id	uuid	Primary key
first_name	text	First name
last_name	text	Last name
email	text	Email address (unique)
slack_ids	text	Slack user ID
department	departments[]	Departments (AI, Data, Sales, Operations, Content)
team	text	Team assignment
title	text	Job title
status	text	Status (default: ‘active’)
client	text[]	Assigned clients
user_id	uuid	Auth user ID (FK to auth.users)

Agents Table

Table: agents (8 rows)

Column	Type	Description
id	uuid	Primary key
agent_name	text	Agent name (unique)
description	text	Agent description
prompt	text	System prompt
category	text	Agent category
featured	boolean	Featured flag
initial_message	text	Initial greeting

Service Offerings Table

Table: service_offerings (166 rows)

Column	Type	Description
id	bigint	Primary key
title	text	Offering title
description	text	Description
type	text	Offering type
content	jsonb	Full content
embedding	vector	Vector embedding for search
sync_id	text	Notion sync ID
is_deleted	boolean	Soft delete flag

Standup Summaries Table

Table: standup_summaries (1,392 rows)

Column	Type	Description
id	uuid	Primary key
client_id	uuid	FK to clients
status_update	jsonb	Status update content
blockers	jsonb	Blockers list
follow_ups	jsonb	Follow-up items
weekly_goals	text	Weekly goals summary
standup_notes	text	Raw standup notes
generation_type	text	’manual’ or ‘auto’
generated_at	timestamptz	Generation timestamp

Supporting Tables

Table	Rows	Description
weekly_goals	8	Weekly goals by client
case_studies	11	Case study records
case_study_interviews	7	Interview transcripts
case_study_copies	7	Generated case study copy
file_metadata	78	Asset/file metadata
files_hits	1,700	File access tracking
circle_back_leads	33	Lead follow-up queue
agent_config	1	Agent configuration
agent_available_models	2	Available LLM models
slack_message_log	24	Slack message audit log

Common Queries

-- Get all active clients with their table mappings
SELECT name, slack_channel_id, slack_messages_table, zoom_embeddings_table, linear_team_id
FROM clients
WHERE active_status = true;
 
-- Get team members by department
SELECT first_name, last_name, email, slack_ids, title
FROM team
WHERE 'AI' = ANY(department) AND status = 'active';
 
-- Get team member by email or Slack ID
SELECT * FROM team WHERE email = 'sam@brainforge.ai';
SELECT * FROM team WHERE slack_ids = 'U12345ABC';
 
-- Get client by name or alias
SELECT * FROM clients WHERE name ILIKE '%eden%' OR 'eden' = ANY(aliases);
 
-- Get recent standup summaries for a client
SELECT s.generated_at, s.status_update, s.blockers, s.follow_ups
FROM standup_summaries s
JOIN clients c ON s.client_id = c.id
WHERE c.name ILIKE '%eden%'
ORDER BY s.generated_at DESC
LIMIT 5;
 
-- Search service offerings
SELECT title, description, type
FROM service_offerings
WHERE title ILIKE '%data%' OR description ILIKE '%data%';

---

Slack Messages Project (oqtkgsndvitzyfzwcdoz)

Table Naming Conventions

Pattern	Description	Example
client_{client}_messages	Raw Slack messages	client_eden_messages
{client}_slack_content_blocks	Aggregated content by period/channel	eden_slack_content_blocks
{client}_transformed	Pre-processed summaries with embeddings	eden_transformed
client_{client}_embeddings	Vector embeddings for semantic search	client_eden_embeddings
internal_{team}_messages	Internal team Slack channels	internal_ai_team_messages

Raw Messages Table Schema

Table: client_{client}_messages

Column	Type	Description
id	integer	Primary key
text	text	Message content
ts	timestamptz	Message timestamp
user_id	text	Slack user ID
real_name	text	User display name
name	text	Username
channel_id	text	Slack channel ID
thread_ts	timestamptz	Thread timestamp (if reply)
reply_count	float8	Number of replies
is_bot	boolean	Whether sender is a bot
is_admin	boolean	Whether sender is admin
blocks	jsonb	Slack block kit data
metadata	jsonb	Additional metadata
embedding	vector	Message embedding
content	text	Processed content

Available client tables:

• client_eden_messages (14,275 rows)
• client_javvy_messages (991 rows)
• client_pp2g_messages (5,351 rows)
• client_abchome_messages (4,263 rows)
• client_urbanstems_messages (8,587 rows)
• client_mattermore_messages (540 rows)
• client_otr_messages (295 rows)
• client_readme_messages (574 rows)
• client_sparkplug_messages (122 rows)
• client_insomniacookies_messages (2,349 rows)
• client_ellie_messages (509 rows)
• client_interlude_messages (260 rows)
• client_rimo_messages (1,068 rows)
• client_hypaccess_messages (77 rows)
• client_hedra_messages (153 rows)
• client_honeystinger_messages (807 rows)
• client_default_messages (2,344 rows)

Content Blocks Table Schema

Table: {client}_slack_content_blocks

Column	Type	Description
id	bigint	Primary key
period	text	Time period (e.g., “2025-01”)
channel	text	Channel name
content	text	Aggregated content for the period
message_count	integer	Number of messages in block
created_at	timestamptz	Block creation time
updated_at	timestamptz	Last update time

Available content block tables:

• eden_slack_content_blocks (136 rows)
• javvy_slack_content_blocks (63 rows)
• pp2g_slack_content_blocks (107 rows)
• abchome_slack_content_blocks (54 rows)
• urbanstems_slack_content_blocks (96 rows)
• mattermore_slack_content_blocks (13 rows)
• otr_slack_content_blocks (19 rows)
• readme_slack_content_blocks (24 rows)
• sparkplug_slack_content_blocks (11 rows)
• insomniacookies_slack_content_blocks (24 rows)
• ellie_slack_content_blocks (29 rows)
• interlude_slack_content_blocks (12 rows)
• rimo_slack_content_blocks (38 rows)
• hypaccess_slack_content_blocks (6 rows)
• hedra_slack_content_blocks (9 rows)
• honeystinger_slack_content_blocks (18 rows)
• default_slack_content_blocks (26 rows)

Embeddings Table Schema (Slack)

Table: client_{client}_embeddings

Column	Type	Description
id	bigint	Primary key
content_block_id	bigint	FK to content blocks
chunk_index	integer	Chunk position
content	text	Chunk content
embedding	vector	Vector embedding
content_hash	text	Content hash for dedup
model_used	text	Embedding model
metadata	jsonb	Additional metadata
created_at	timestamp	Creation time
updated_at	timestamp	Last update time

Internal Team Tables

Table	Rows	Description
internal_ai_team_messages	26,903	AI team channel
internal_operations_messages	6,778	Operations channel
internal_marketing_messages	12,325	Marketing channel
internal_sales_messages	11,143	Sales channel
internal_project_management_messages	4,417	PM channel

---

Zoom Transcripts Project (viqeppmsqvwpslpvttkk)

Table Naming Conventions

Pattern	Description	Example
zoom_meeting_recording_files	All meetings (master table)	-
client_{client}_raw	Client-filtered meeting views	client_eden_raw
client_{client}_embeddings	Transcript embeddings	client_eden_embeddings
linear_ticket_generations	Generated Linear tickets	-

Master Meeting Table Schema

Table: zoom_meeting_recording_files (63 rows)

Column	Type	Description
id	uuid	Primary key
s3_path	text	S3 storage path (unique)
folder	text	Meeting folder/topic name
content	text	Full transcript content
summary	text	AI-generated summary
meeting_date	date	Meeting date
participants	jsonb	Attendee information
team	text	Associated team
departments	int8[]	Department IDs
is_public	boolean	Public sharing flag
video_s3_paths	jsonb	Video file paths
video_urls	jsonb	Video URLs
created_at	timestamptz	Record creation time

Client Raw Views Schema

Table: client_{client}_raw

Same schema as zoom_meeting_recording_files, filtered by client.

Table	Rows	Client
client_eden_raw	233	Eden
client_abc_home_and_commercial_raw	296	ABC Home
client_urban_stems_raw	137	Urban Stems
client_default_raw	83	Default/Misc
client_interlude_raw	45	Interlude
client_pool_parts_to_go_raw	40	Pool Parts To Go
client_readme_raw	34	Readme
client_ellie_raw	32	Ellie
client_honeystinger_raw	26	Honey Stinger
client_hyp_access_raw	24	Hyp Access
client_rimo_raw	11	Rimo
client_insomnia_cookies_raw	10	Insomnia Cookies
client_hedra_raw	5	Hedra

Transcript Embeddings Schema

Table: client_{client}_embeddings

Column	Type	Description
id	bigint	Primary key
source_record_id	uuid	FK to zoom_meeting_recording_files
content	text	Transcript chunk
embedding	vector	Vector embedding
metadata	jsonb	Chunk metadata
created_at	timestamp	Creation time

Available embedding tables:

• client_eden_embeddings (1,237 rows)
• client_abc home and commercial_embeddings (1,662 rows)
• client_urban stems_embeddings (570 rows)
• client_default_embeddings (547 rows)
• client_pool parts to go_embeddings (209 rows)
• client_readme_embeddings (228 rows)
• client_interlude_embeddings (214 rows)
• client_honeystinger_embeddings (181 rows)
• client_ellie_embeddings (164 rows)
• client_hyp access_embeddings (157 rows)
• client_insomnia cookies_embeddings (90 rows)
• client_hedra_embeddings (64 rows)
• client_rimo_embeddings (54 rows)

Linear Ticket Generations Schema

Table: linear_ticket_generations (1,620 rows)

Column	Type	Description
id	uuid	Primary key
meeting_id	uuid	FK to zoom_meeting_recording_files
title	text	Ticket title
description	text	Ticket description
team_id	text	Linear team ID
team_name	text	Linear team name
assignee_id	text	Assignee ID
assignee_name	text	Assignee name
linear_issue_id	text	Created Linear issue ID
linear_issue_url	text	Issue URL
linear_issue_identifier	text	Issue identifier (e.g., “BF-123”)
status	text	Generation status
links	jsonb	Related links
preferred_state	jsonb	Desired issue state
generation_metadata	jsonb	Generation details
created_at	timestamptz	Creation time
updated_at	timestamptz	Last update
created_in_linear_at	timestamptz	When pushed to Linear

---

Common Query Examples

Slack Queries

-- Get recent content blocks for Eden
SELECT period, channel, content, message_count 
FROM eden_slack_content_blocks 
ORDER BY period DESC 
LIMIT 5;
 
-- Search messages containing a keyword
SELECT ts, real_name, text 
FROM client_eden_messages 
WHERE text ILIKE '%roadmap%' 
ORDER BY ts DESC;
 
-- Get messages from a specific date range
SELECT ts, real_name, text 
FROM client_urbanstems_messages 
WHERE ts >= '2025-01-01' AND ts < '2025-02-01'
ORDER BY ts;

Zoom Queries

-- Get recent meetings for a client
SELECT meeting_date, folder, summary 
FROM client_eden_raw 
ORDER BY meeting_date DESC 
LIMIT 5;
 
-- Get full transcript for latest meeting
SELECT content, summary, participants 
FROM client_eden_raw 
ORDER BY meeting_date DESC 
LIMIT 1;
 
-- Search all meetings by keyword
SELECT id, meeting_date, folder, summary 
FROM zoom_meeting_recording_files 
WHERE summary ILIKE '%integration%'
ORDER BY meeting_date DESC;
 
-- Get meeting with embeddings for semantic search context
SELECT e.content, r.meeting_date, r.folder 
FROM client_eden_embeddings e 
JOIN zoom_meeting_recording_files r ON e.source_record_id = r.id 
WHERE r.meeting_date >= '2025-01-01' 
ORDER BY r.meeting_date DESC, e.id;
 
-- Get generated tickets for a meeting
SELECT title, description, status, linear_issue_url 
FROM linear_ticket_generations 
WHERE meeting_id = 'uuid-here';

---

Client Name Mapping

When a user references a client, use this mapping to find the correct table name:

User Input	Table Key	Notes
”Eden”	eden
”Javvy”	javvy
”Pool Parts”, “PP2G”	pp2g (Slack), pool_parts_to_go (Zoom)	Different conventions
”ABC Home”, “ABC”	abchome (Slack), abc_home_and_commercial (Zoom)
“Urban Stems”	urbanstems (Slack), urban_stems (Zoom)
“Mattermore”	mattermore	Slack only
”OTR”	otr
”Readme”	readme
”Sparkplug”	sparkplug
”Insomnia”, “Insomnia Cookies”	insomniacookies (Slack), insomnia_cookies (Zoom)
“Ellie”, “Ellie Mental Health”	ellie
”Interlude”	interlude
”Rimo”	rimo
”Hyp Access”, “HypAccess”	hypaccess (Slack), hyp_access (Zoom)
“Hedra”	hedra
”Honey Stinger”	honeystinger

---

Notes

• Vector columns: The embedding columns use pgvector type for semantic search
• Table name quirks: Some Zoom tables have spaces in names (e.g., client_pool parts to go_embeddings) - use double quotes in SQL
• Date formats: Use ISO format (YYYY-MM-DD) for date filters
• Timestamps: All timestamps are in UTC