[Low] Most user_id columns are text — no FK referential integrity to auth.users

[bmersereau]

Problem

backend/schema.sql — user_profiles and user_api_keys correctly use uuid references auth.users(id) on delete cascade, but the majority of application tables use a bare text column:

• projects.user_id text not null
• documents.user_id text not null
• chats.user_id text not null
• tabular_reviews.user_id text not null
• workflows.user_id text
• project_subfolders.user_id text not null
• workflow_shares.shared_by_user_id text not null

Postgres cannot enforce that stored user IDs correspond to real Supabase users. A data migration error, a bulk import, or a future code bug could silently insert a user ID that doesn't exist in auth.users, and Postgres would accept it without complaint.

Fix

Migrate user_id text columns to uuid references auth.users(id) on delete cascade. This is a breaking schema change — the migration should:

1. ALTER COLUMN user_id TYPE uuid USING user_id::uuid (fails fast on non-UUID values).
2. Verify no rows reference non-existent users before adding the FK.
3. Add ON DELETE CASCADE or ON DELETE SET NULL as appropriate per table.