name: Database Schema Cleanup overview: "Audit and reorganize the shared Supabase database from a flat 54-table public schema into clean, schema-separated domains: consul, generative, and consulate -- while dropping the abandoned bookie tables and consolidating redundant trigger functions." todos:

id: drop-bookie content: Write migration SQL to drop all 5 bookie tables and the bookie trigger function status: pending
id: create-schemas content: Write migration SQL to create consul, generative, and consulate schemas with proper grants status: pending
id: move-consul-tables content: Write migration SQL to move and rename 15 consul tables + 4 functions + 5 enums status: pending
id: move-generative-tables content: Write migration SQL to move 31 generative tables + 11 functions + 1 view + enums status: pending
id: move-consulate-tables content: Write migration SQL to move and rename 3 consulate tables + 1 function status: pending
id: consolidate-triggers content: Audit which tables use which updated_at trigger, consolidate to one function status: pending
id: update-consul-code content: Update Consul Website Supabase client calls to use consul schema with stripped prefixes status: pending
id: test-on-branch content: Create Supabase branch, apply migration, and verify with pnpm build status: pending isProject: false

Database Schema Cleanup and Reorganization

Current State: 54 Tables in `public`, 4 Systems, No Organization

The database currently has 54 tables, 1 view, 22 functions, and ~20 custom enums all dumped into the public schema. Four distinct systems share the database with inconsistent naming: some prefixed (consul_, investor_, bookie_*), some generic (articles, authors, categories), some confusingly similar (consul_* vs consulate_*).

Phase 1: Drop Bookie Tables (5 tables, 1 function)

These are abandoned and should be removed first. Drop order matters due to FK constraints:

DROP TABLE IF EXISTS bookie_bookmark_categories CASCADE;
DROP TABLE IF EXISTS bookie_sync_status CASCADE;
DROP TABLE IF EXISTS bookie_bookmarks CASCADE;
DROP TABLE IF EXISTS bookie_categories CASCADE;
DROP TABLE IF EXISTS bookie_users CASCADE;
DROP FUNCTION IF EXISTS bookie_update_updated_at_column CASCADE;

Data impact: 595 bookmarks, 110 bookmark-category links, 5 categories, 1 user, 1 sync status row.

Phase 2: Create Schemas and Move Tables

Schema: `consul` (Consul Website -- consul.so)

15 tables. Strip consul_ prefix since the schema provides the namespace.

Current Name (public)	New Name (consul)	Rows
`consul_posts`	`consul.posts`	32
`consul_authors`	`consul.authors`	1
`consul_categories`	`consul.categories`	4
`consul_keywords`	`consul.keywords`	173
`consul_post_keywords`	`consul.post_keywords`	184
`consul_post_relationships`	`consul.post_relationships`	0
`consul_post_validations`	`consul.post_validations`	31
`consul_post_performance`	`consul.post_performance`	0
`admin_users`	`consul.admin_users`	3
`admin_login_attempts`	`consul.admin_login_attempts`	0
`guide_leads`	`consul.guide_leads`	1
`consulting_inquiries`	`consul.consulting_inquiries`	0
`audit_responses`	`consul.audit_responses`	0
`audit_scores`	`consul.audit_scores`	0
`audit_leads`	`consul.audit_leads`	0

Functions to move:

consul_calculate_reading_time -> consul.calculate_reading_time
consul_get_related_posts -> consul.get_related_posts
consul_posts_search_vector_update -> consul.posts_search_vector_update
consul_update_reading_time -> consul.update_reading_time

Enums to move: consul_content_type, consul_cta_type, consul_relationship_type, consul_schema_type, consul_update_subtype

Schema: `generative` (Generative Website)

31 tables. Keep sub-domain prefixes (investor_, manifesto_, simple_*, competitor_*) since they represent distinct features within Generative.

Current Name (public)	New Name (generative)	Rows
`articles`	`generative.articles`	13
`authors`	`generative.authors`	3
`categories`	`generative.categories`	4
`tags`	`generative.tags`	41
`article_tags`	`generative.article_tags`	20
`article_images`	`generative.article_images`	0
`documents`	`generative.documents`	58
`simple_articles`	`generative.simple_articles`	98
`simple_source_references`	`generative.simple_source_references`	171
`simple_agent_runs`	`generative.simple_agent_runs`	26
`competitors`	`generative.competitors`	33
`competitor_products`	`generative.competitor_products`	33
`investor_updates`	`generative.investor_updates`	4
`investor_contacts`	`generative.investor_contacts`	4
`investor_sends`	`generative.investor_sends`	3
`investor_email_events`	`generative.investor_email_events`	0
`investor_organizations`	`generative.investor_organizations`	0
`investor_contact_organizations`	`generative.investor_contact_organizations`	0
`investor_resend_sync_jobs`	`generative.investor_resend_sync_jobs`	4
`investor_resend_sync_runs`	`generative.investor_resend_sync_runs`	38
`investor_contact_interactions`	`generative.investor_contact_interactions`	0
`investor_lists`	`generative.investor_lists`	0
`investor_contact_lists`	`generative.investor_contact_lists`	0
`investor_tags`	`generative.investor_tags`	0
`investor_contact_tags`	`generative.investor_contact_tags`	0
`investor_update_tags`	`generative.investor_update_tags`	0
`manifesto_access_attempts`	`generative.manifesto_access_attempts`	72
`manifesto_page_access_rules`	`generative.manifesto_page_access_rules`	6
`manifesto_sessions`	`generative.manifesto_sessions`	12
`manifesto_page_rule_tags`	`generative.manifesto_page_rule_tags`	0
`internal_users`	`generative.internal_users`	2

Functions to move: get_related_articles, increment_article_views, increment_investor_send_click, increment_investor_send_open, increment_open_count, investor_access_tag_rank, investor_get_eligible_contacts, match_articles_by_embedding, search_articles, is_internal_admin, is_internal_member

View to move: published_articles_view -> generative.published_articles_view

Enums to move: investor_* types, manifesto-related types, product_* types, data_quality_level, pricing_model, target_user, context_link_type, access_tag, tag_match_mode

Schema: `consulate` (Consulate -- separate project)

3 tables. Strip consulate_ prefix.

Current Name (public)	New Name (consulate)	Rows
`consulate_team_members`	`consulate.team_members`	3
`consulate_tasks`	`consulate.tasks`	52
`consulate_requests`	`consulate.requests`	7

Function to move: set_consulate_requests_updated_at -> consulate.set_requests_updated_at

Phase 3: Consolidate Trigger Functions

There are 5 nearly identical updated_at trigger functions in public:

set_updated_at
set_updated_at_timestamp
update_last_updated
update_updated_at
update_updated_at_column

Action: Keep one canonical public.set_updated_at(), reassign all triggers to use it, then drop the rest.

Phase 4: Supabase Configuration

For the new schemas to work with the Supabase JS client:

Supabase Dashboard -> Settings -> API -> Exposed Schemas: Add consul, generative, consulate
Grant roles: Each schema needs USAGE and appropriate SELECT/INSERT/UPDATE/DELETE grants on anon, authenticated, and service_role
RLS policies: Preserved during ALTER TABLE SET SCHEMA, but verify after migration

Phase 5: Application Code Changes

5a. Consul Website (this repo -- consul.so)

The Consul Website Supabase client calls need to target the consul schema. Key files:

lib/supabase/content.ts -- 14 calls to consul_posts, 1 RPC call to consul_get_related_posts
lib/supabase/admin-content.ts -- queries consul_posts, consul_authors, consul_post_keywords, consul_keywords
lib/supabase/admin-login-action.ts -- queries admin_users
app/admin/(dashboard)/layout.tsx -- queries admin_users
lib/guide-leads.ts -- queries guide_leads

Change pattern: Create a schema-aware Supabase client helper:

const consulDb = supabase.schema('consul');
// Then: consulDb.from('posts') instead of supabase.from('consul_posts')

All .from("consul_posts") becomes .from("posts") on the consul schema client. All .rpc("consul_get_related_posts") becomes .rpc("get_related_posts") on the schema client.

Full find-and-replace map for Consul Website:

.from("consul_posts") -> .schema('consul').from("posts")
.from("consul_authors") -> .schema('consul').from("authors")
.from("consul_post_keywords") -> .schema('consul').from("post_keywords")
.from("consul_keywords") -> .schema('consul').from("keywords")
.from("admin_users") -> .schema('consul').from("admin_users")
.from("guide_leads") -> .schema('consul').from("guide_leads")
.rpc("consul_get_related_posts", ...) -> .schema('consul').rpc("get_related_posts", ...)

5b. Generative Website (separate repo)

The Generative Website needs to target the generative schema. Since table names within the generative schema stay the same (no prefix stripping), the only change is adding the schema qualifier.

Supabase client setup -- add a schema-aware client:

// Before
const supabase = createClient(url, key);
supabase.from("articles")...

// After
const genDb = createClient(url, key, { db: { schema: 'generative' } });
// OR per-query:
const genDb = supabase.schema('generative');
genDb.from("articles")...

If the Generative app is the primary consumer of this database, consider setting the default schema in the client config so most code doesn't change:

const supabase = createClient(url, key, {
  db: { schema: 'generative' }
});
// Now supabase.from("articles") automatically targets generative.articles

Table name changes -- none. The Generative tables keep their existing names; they just move to the generative schema. Every .from("articles"), .from("investor_contacts"), .from("simple_articles"), etc. continues to work as-is once the schema is set.

RPC function changes:

.rpc("get_related_articles", ...) -- no rename, but must target generative schema
.rpc("increment_article_views", ...) -- same
.rpc("increment_investor_send_click", ...) -- same
.rpc("increment_investor_send_open", ...) -- same
.rpc("increment_open_count", ...) -- same
.rpc("investor_access_tag_rank", ...) -- same
.rpc("investor_get_eligible_contacts", ...) -- same
.rpc("match_articles_by_embedding", ...) -- same
.rpc("search_articles", ...) -- same
.rpc("is_internal_admin", ...) -- same
.rpc("is_internal_member", ...) -- same

Checklist of files to grep and update in Generative repo:

# Find all Supabase client calls that need the schema qualifier
rg '\.from\(|\.rpc\(' --type ts --type tsx

# Specifically look for these table references:
rg 'from\("(articles|authors|categories|tags|article_tags|article_images|documents|simple_|investor_|manifesto_|competitors|competitor_products|internal_users|published_articles_view)"' --type ts

Edge Functions: If any Supabase Edge Functions query these tables, they also need the schema qualifier. Check the supabase/functions/ directory.

Database triggers and policies: RLS policies move with the tables automatically. However, any policies that reference public.table_name in their definitions will need updating. Run this after migration to check:

SELECT schemaname, tablename, policyname, qual, with_check
FROM pg_policies
WHERE qual LIKE '%public.%' OR with_check LIKE '%public.%';

5c. Consulate (separate project)

The Consulate app needs to target the consulate schema, and table names lose their consulate_ prefix.

Supabase client setup:

const consulateDb = createClient(url, key, {
  db: { schema: 'consulate' }
});
// OR per-query:
const consulateDb = supabase.schema('consulate');

Find-and-replace map for Consulate:

.from("consulate_team_members") -> .from("team_members") (on consulate schema)
.from("consulate_tasks") -> .from("tasks") (on consulate schema)
.from("consulate_requests") -> .from("requests") (on consulate schema)
.rpc("set_consulate_requests_updated_at") -> .rpc("set_requests_updated_at") (on consulate schema)

Checklist:

rg 'consulate_' --type ts --type tsx

Only 3 tables and 1 function to update -- this is the smallest change set.

Phase 6: Transition Safety Net (Optional)

To avoid a hard-cutover, create temporary views in public that alias old names to new locations. This lets old code keep working while each app is updated independently:

-- Example: bridge view for consul_posts
CREATE VIEW public.consul_posts AS SELECT * FROM consul.posts;
-- Example: bridge view for articles  
CREATE VIEW public.articles AS SELECT * FROM generative.articles;
-- Example: bridge view for consulate_tasks
CREATE VIEW public.consulate_tasks AS SELECT * FROM consulate.tasks;

These bridge views support SELECT queries out of the box. For INSERT/UPDATE/DELETE, you'd need INSTEAD OF triggers on each view, which adds complexity. If all three apps can deploy simultaneously, skip this phase entirely.

Teardown: Once all apps are confirmed working on the new schemas, drop the bridge views:

DROP VIEW IF EXISTS public.consul_posts;
DROP VIEW IF EXISTS public.articles;
-- etc.

Migration Strategy

This should be executed as a single Supabase migration with coordinated app deploys:

Create a Supabase branch for testing
Write the migration SQL (schema creation, table moves, renames, grants)
Update Consul Website code (this repo) to use the consul schema
Coordinate with Generative Website team to update their code simultaneously
Coordinate with Consulate project to update their code
Deploy migration + all app updates together (or use public-schema views as a temporary bridge)

Rollback safety: The migration can include temporary views in public that alias old table names to new schema locations, allowing a gradual transition.