chihlasm/resolutionflow
1
0
Fork 0
Code Issues 23 Pull Requests 1 Actions Packages Projects Releases Wiki Activity

feat: Phase 1 tenant isolation — add account_id to all tenant tables #133

Merged
chihlasm merged 37 commits from feat/tenant-isolation-phase-1 into main 2026-04-10 04:57:53 +00:00
Conversation 1 Commits 37 Files Changed 65 +5134 -169
chihlasm commented 2026-04-09 05:36:56 +00:00 (Migrated from github.com)
Copy Link

Summary

  • Adds account_id (tenant boundary) to all 28 tenant tables that lacked it, following the non-negotiable migration sequence: ADD nullable → backfill via JOIN → verify zero NULLs → SET NOT NULL → CREATE INDEX
  • Creates template_trees and platform_steps global content tables (no account_id, no RLS — readable by all authenticated users)
  • Creates platform sentinel account (00000000-0000-0000-0000-000000000001) for global content (is_default trees, public steps, global categories/tags)
  • Enforces NOT NULL on all previously-nullable account_id columns
  • Keeps team_id in script tables and target_lists (app code migration deferred)

Migrations (10 total, in dependency order)

Migration Tables
Group 1 sessions, attachments, session_supporting_data, session_resolution_outputs
Group 2 session_branches, session_handoffs, fork_points, ai_session_steps, ai_suggestions
Group 3 step_ratings, step_usage_log
Group 4 user_folders, user_pinned_trees
Group 5 psa_post_log, psa_member_mappings, notification_logs
Group 6 maintenance_schedules
Group 7 script_builder_sessions, script_templates, script_generations
Group 8 target_lists
Group 10 CREATE template_trees, platform_steps; assign platform account to global content
Group 9 SET NOT NULL on users, trees, tree_categories, tree_tags, step_categories, step_library, tree_embeddings, feedback

Gate verification

After merging, run from VPS SSH to confirm zero NULLs across all tenant tables:

docker exec -it resolutionflow_postgres psql -U postgres -d resolutionflow -c "
SELECT tablename, null_count FROM (
    SELECT 'sessions' AS tablename, COUNT(*) FILTER (WHERE account_id IS NULL) AS null_count FROM sessions
    UNION ALL SELECT 'attachments', COUNT(*) FILTER (WHERE account_id IS NULL) FROM attachments
    UNION ALL SELECT 'session_supporting_data', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_supporting_data
    UNION ALL SELECT 'session_resolution_outputs', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_resolution_outputs
    UNION ALL SELECT 'session_branches', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_branches
    UNION ALL SELECT 'session_handoffs', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_handoffs
    UNION ALL SELECT 'fork_points', COUNT(*) FILTER (WHERE account_id IS NULL) FROM fork_points
    UNION ALL SELECT 'ai_session_steps', COUNT(*) FILTER (WHERE account_id IS NULL) FROM ai_session_steps
    UNION ALL SELECT 'ai_suggestions', COUNT(*) FILTER (WHERE account_id IS NULL) FROM ai_suggestions
    UNION ALL SELECT 'step_ratings', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_ratings
    UNION ALL SELECT 'step_usage_log', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_usage_log
    UNION ALL SELECT 'user_folders', COUNT(*) FILTER (WHERE account_id IS NULL) FROM user_folders
    UNION ALL SELECT 'user_pinned_trees', COUNT(*) FILTER (WHERE account_id IS NULL) FROM user_pinned_trees
    UNION ALL SELECT 'psa_post_log', COUNT(*) FILTER (WHERE account_id IS NULL) FROM psa_post_log
    UNION ALL SELECT 'psa_member_mappings', COUNT(*) FILTER (WHERE account_id IS NULL) FROM psa_member_mappings
    UNION ALL SELECT 'notification_logs', COUNT(*) FILTER (WHERE account_id IS NULL) FROM notification_logs
    UNION ALL SELECT 'maintenance_schedules', COUNT(*) FILTER (WHERE account_id IS NULL) FROM maintenance_schedules
    UNION ALL SELECT 'script_builder_sessions', COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_builder_sessions
    UNION ALL SELECT 'script_templates', COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_templates
    UNION ALL SELECT 'script_generations', COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_generations
    UNION ALL SELECT 'target_lists', COUNT(*) FILTER (WHERE account_id IS NULL) FROM target_lists
    UNION ALL SELECT 'trees', COUNT(*) FILTER (WHERE account_id IS NULL) FROM trees
    UNION ALL SELECT 'tree_categories', COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_categories
    UNION ALL SELECT 'tree_tags', COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_tags
    UNION ALL SELECT 'step_categories', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_categories
    UNION ALL SELECT 'step_library', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_library
    UNION ALL SELECT 'tree_embeddings', COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_embeddings
    UNION ALL SELECT 'feedback', COUNT(*) FILTER (WHERE account_id IS NULL) FROM feedback
) t WHERE null_count > 0;
"

Expected output: 0 rows (all tables have zero NULLs).

Known follow-ups

  • feedback.user_id has ondelete="SET NULL" but nullable=False — pre-existing contradiction, separate fix needed before user deletion path is exercised
  • TreeTag.is_global property always returns False now (account_id is NOT NULL) — property logic needs updating
  • CI grep check: switch check_tenant_filters.py from warn to --fail mode after app code migration completes
  • Phase 2: enable RLS + set app.current_account_id in get_db() dependency

🤖 Generated with Claude Code

## Summary - Adds `account_id` (tenant boundary) to all 28 tenant tables that lacked it, following the non-negotiable migration sequence: ADD nullable → backfill via JOIN → verify zero NULLs → SET NOT NULL → CREATE INDEX - Creates `template_trees` and `platform_steps` global content tables (no account_id, no RLS — readable by all authenticated users) - Creates platform sentinel account (`00000000-0000-0000-0000-000000000001`) for global content (is_default trees, public steps, global categories/tags) - Enforces NOT NULL on all previously-nullable account_id columns - Keeps `team_id` in script tables and target_lists (app code migration deferred) ## Migrations (10 total, in dependency order) | Migration | Tables | |---|---| | Group 1 | sessions, attachments, session_supporting_data, session_resolution_outputs | | Group 2 | session_branches, session_handoffs, fork_points, ai_session_steps, ai_suggestions | | Group 3 | step_ratings, step_usage_log | | Group 4 | user_folders, user_pinned_trees | | Group 5 | psa_post_log, psa_member_mappings, notification_logs | | Group 6 | maintenance_schedules | | Group 7 | script_builder_sessions, script_templates, script_generations | | Group 8 | target_lists | | Group 10 | CREATE template_trees, platform_steps; assign platform account to global content | | Group 9 | SET NOT NULL on users, trees, tree_categories, tree_tags, step_categories, step_library, tree_embeddings, feedback | ## Gate verification After merging, run from VPS SSH to confirm zero NULLs across all tenant tables: ```bash docker exec -it resolutionflow_postgres psql -U postgres -d resolutionflow -c " SELECT tablename, null_count FROM ( SELECT 'sessions' AS tablename, COUNT(*) FILTER (WHERE account_id IS NULL) AS null_count FROM sessions UNION ALL SELECT 'attachments', COUNT(*) FILTER (WHERE account_id IS NULL) FROM attachments UNION ALL SELECT 'session_supporting_data', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_supporting_data UNION ALL SELECT 'session_resolution_outputs', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_resolution_outputs UNION ALL SELECT 'session_branches', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_branches UNION ALL SELECT 'session_handoffs', COUNT(*) FILTER (WHERE account_id IS NULL) FROM session_handoffs UNION ALL SELECT 'fork_points', COUNT(*) FILTER (WHERE account_id IS NULL) FROM fork_points UNION ALL SELECT 'ai_session_steps', COUNT(*) FILTER (WHERE account_id IS NULL) FROM ai_session_steps UNION ALL SELECT 'ai_suggestions', COUNT(*) FILTER (WHERE account_id IS NULL) FROM ai_suggestions UNION ALL SELECT 'step_ratings', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_ratings UNION ALL SELECT 'step_usage_log', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_usage_log UNION ALL SELECT 'user_folders', COUNT(*) FILTER (WHERE account_id IS NULL) FROM user_folders UNION ALL SELECT 'user_pinned_trees', COUNT(*) FILTER (WHERE account_id IS NULL) FROM user_pinned_trees UNION ALL SELECT 'psa_post_log', COUNT(*) FILTER (WHERE account_id IS NULL) FROM psa_post_log UNION ALL SELECT 'psa_member_mappings', COUNT(*) FILTER (WHERE account_id IS NULL) FROM psa_member_mappings UNION ALL SELECT 'notification_logs', COUNT(*) FILTER (WHERE account_id IS NULL) FROM notification_logs UNION ALL SELECT 'maintenance_schedules', COUNT(*) FILTER (WHERE account_id IS NULL) FROM maintenance_schedules UNION ALL SELECT 'script_builder_sessions', COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_builder_sessions UNION ALL SELECT 'script_templates', COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_templates UNION ALL SELECT 'script_generations', COUNT(*) FILTER (WHERE account_id IS NULL) FROM script_generations UNION ALL SELECT 'target_lists', COUNT(*) FILTER (WHERE account_id IS NULL) FROM target_lists UNION ALL SELECT 'trees', COUNT(*) FILTER (WHERE account_id IS NULL) FROM trees UNION ALL SELECT 'tree_categories', COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_categories UNION ALL SELECT 'tree_tags', COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_tags UNION ALL SELECT 'step_categories', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_categories UNION ALL SELECT 'step_library', COUNT(*) FILTER (WHERE account_id IS NULL) FROM step_library UNION ALL SELECT 'tree_embeddings', COUNT(*) FILTER (WHERE account_id IS NULL) FROM tree_embeddings UNION ALL SELECT 'feedback', COUNT(*) FILTER (WHERE account_id IS NULL) FROM feedback ) t WHERE null_count > 0; " ``` Expected output: 0 rows (all tables have zero NULLs). ## Known follow-ups - `feedback.user_id` has `ondelete="SET NULL"` but `nullable=False` — pre-existing contradiction, separate fix needed before user deletion path is exercised - `TreeTag.is_global` property always returns False now (account_id is NOT NULL) — property logic needs updating - CI grep check: switch `check_tenant_filters.py` from warn to `--fail` mode after app code migration completes - Phase 2: enable RLS + set `app.current_account_id` in `get_db()` dependency 🤖 Generated with [Claude Code](https://claude.com/claude-code)
railway-app[bot] commented 2026-04-09 05:37:10 +00:00 (Migrated from github.com)
Copy Link

🚅 Deployed to the resolutionflow-pr-133 environment in selfless-grace

Service Status Web Updated (UTC)
hopeful-liberation 🕒 Building (View Logs) Web Apr 10, 2026 at 4:57 am
patherly 🕒 Building (View Logs) Web Apr 10, 2026 at 4:57 am
<!-- railway-bot-comment-version=2 --> <!-- railway-project-id="22b9b58c-271b-42e5-a10e-6fdec8d00134" railway-project-name="selfless-grace" --> 🚅 Deployed to the [resolutionflow-pr-133](https://railway.com/project/22b9b58c-271b-42e5-a10e-6fdec8d00134?environmentId=8a37258b-7188-414d-b6cc-bdbca8d351e7) environment in **[selfless-grace](https://railway.com/project/22b9b58c-271b-42e5-a10e-6fdec8d00134)** | **Service** | **Status** | **Web** | **Updated** (UTC) | | :--- | :--- | :--- | :--- | | hopeful-liberation | 🕒 Building ([View Logs](https://railway.com/project/22b9b58c-271b-42e5-a10e-6fdec8d00134/service/e1db2ee3-d241-4f45-abe4-c9c5fdf483d5?id=f612e391-aadb-4cf2-ad00-5723aac69ec1&environmentId=8a37258b-7188-414d-b6cc-bdbca8d351e7)) | [Web](https://hopeful-liberation-resolutionflow-pr-133.up.railway.app) | Apr 10, 2026 at 4:57 am | | patherly | 🕒 Building ([View Logs](https://railway.com/project/22b9b58c-271b-42e5-a10e-6fdec8d00134/service/95f556ff-5264-4116-a0c2-618a2fc53ba4?id=0572f2cd-2543-41a3-b5a0-c28456de92ed&environmentId=8a37258b-7188-414d-b6cc-bdbca8d351e7)) | [Web](https://patherly-resolutionflow-pr-133.up.railway.app) | Apr 10, 2026 at 4:57 am |
Sign in to join this conversation.
Reviewers
No Reviewers
Labels
Clear labels
10x-analysis
From 10x product analysis
 backend
FastAPI/Python work
 bug
Something isn't working
 collaboration
Team sharing, escalation, notifications
 database
PostgreSQL/schema changes
 documentation
Documentation updates
 duplicate
This issue or pull request already exists
 enhancement
Improvement to existing feature
 feature
New functionality
 frontend
React work
 good first issue
Good for newcomers
 help wanted
Extra attention is needed
 integration
PSA, external service integrations
 intelligence
Analytics, AI, data-driven features
 invalid
This doesn't seem right
 priority: high
Do this soon
 priority: low
Nice to have
 priority: medium
Important but not urgent
 question
Further information is requested
 quick-win
Low effort, high value
 strategic
Long-term strategic bet
 ux
User experience improvements
 wontfix
This will not be worked on
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
chihlasm
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: chihlasm/resolutionflow#133
Delete Branch "feat/tenant-isolation-phase-1"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?