astrasera — Admin Hub

1

HIPAA RLS Audit

001_hipaa_rls_audit.sql

Enables RLS on all tables, sets up audit logging for HIPAA Technical Safeguards compliance.

2

Profiles RLS

002_profiles_rls.sql

Adds row-level security to the profiles table (contains PHI). Adds is_admin() helper.

3

PHI Notes Tables

003_phi_notes_tables.sql

Moves session notes, client notes, and day notes from localStorage into Supabase (encrypted, RLS-protected).

4

Therapist–Client Connections

004_therapist_clients.sql

Core connections table linking therapists to clients with pending / active / declined / ended status.

5

Invite Tokens

005_invite_tokens.sql

Unique shareable invite tokens per therapist — enables direct client connection via invite link.

6

Admin System

006_admin_system.sql

Adds admin_granted_by / admin_granted_at audit columns. Admins can only be promoted via SQL Editor.

7

Session Transcripts

007_session_transcripts.sql

Persistent transcript storage for AI catch-up. Saved when a therapist ends a session.

8

Username

008_username.sql

Unique @username on profiles — uniqueness constraint, 14-day change cooldown, username_available() RPC.

9

Profile Setup Complete

009_profile_complete.sql

Adds profile_setup_complete boolean — single unambiguous gate for the profile setup page.

10

User Search RPC

010_user_search.sql

SECURITY DEFINER function for cross-role @username search. Returns only public non-PHI fields.

11

Clinician Type

011_clinician_type.sql

Adds clinician_type column — licensed_therapist or life_coach. Updates search RPC.

12

Calendar Events

012_calendar_events.sql

Creates calendar_events and calendar_event_shares tables with RLS. Enables live Supabase-backed calendar.

13

Username Login

013_username_login.sql

Adds get_email_by_username() RPC so users can sign in with @username instead of email.

14

Friends & Messaging

014_friends_messages.sql

Creates friendships and messages tables with RLS. Enables connections and direct messaging.

15

Connection Labels

015_connection_labels.sql

Adds connection label columns and 8 helper RPCs: send, accept, decline, relabel, remove, and list connections.

16

Calendar RLS Fix

016_fix_calendar_rls_recursion.sql

Fixes infinite recursion in calendar_events RLS policies caused by the shared-read policy self-referencing.

17

Session Analysis

017_session_analysis.sql

Creates session_analysis table and adds behavioral_summary JSONB to session_transcripts. Powers AI session analysis.

18

Client Journal

018_client_journal.sql

Creates journal_entries table. Moves client journal from localStorage to Supabase with auto-migration on first login.

19

Practice Settings

019_practice_settings.sql

Adds license_type, session_rate, specialties, modality, and more to profiles. Powers the Practice Settings form.

20

Assignments

020_assignments.sql

Creates assignments, submissions, feedback, and orders tables. Full therapist → client assignment workflow.

21

Assignment Files Bucket

021_storage_bucket.sql

Creates assignment-files Storage bucket (public, 10 MB) with RLS. Required for file attachments in submissions.

22

Security: Restrict User Roles

022_security_fix_user_roles.sql

Revokes PostgREST access to public.user_roles from anon/authenticated. Fixes Security Advisor warnings.

23

Security Warnings Fix

023_security_warnings_fix.sql

Addresses remaining Supabase Security Advisor warnings flagged after migration 022.

24

Clinician Tasks

024_clinician_tasks.sql

Creates clinician_tasks table with RLS. Stores per-clinician to-do items. Powers the dashboard To-Do section.

25

Therapist Availability

025_availability.sql

Creates availability_slots table with RLS. Weekly recurring availability windows per therapist.

26

Clinical Notes — Client ID

026_clinical_notes_client_id.sql

Adds client_id uuid FK to client_notes. Clinical Notes section now linked to real client profiles.

33

Private Notes

033_private_notes.sql

Creates private_notes table with full RLS. Backs the Private Notes section on the client dashboard with live Supabase CRUD.

34

Therapist Journal Read Policy

034_therapist_journal_insights.sql

Adds RLS policy allowing therapists to read journal_entries for linked clients. Enables live Journal Insights in the therapist client panel.

35

Presence — last_active_at

035_last_active_at.sql

Adds last_active_at TIMESTAMPTZ to profiles. user.js heartbeat pings every 2 min; therapist dashboard shows Active / Idle / Disconnected chips.

36

Journal Notes

036_journal_notes.sql

Creates journal_notes table (user_id, entry_id, body, quote) with RLS. Backs the Notes tab on the client journal with Supabase CRUD.

37

Backfill client_notes.client_id

037_backfill_client_notes_client_id.sql

Populates client_id on existing client_notes rows by matching client_name against connected client profiles. Fixes Journal Insights for pre-026 notes.

38

Billing & Invoices

038_billing_invoices.sql

Creates invoices table with RLS. Tracks therapist invoices with status, amounts, due dates, and optional calendar event linkage for auto-invoicing.

39

Billing Payment Methods

039_billing_payment_methods.sql

Creates payment_methods table with RLS for storing client payment info references (Stripe tokens, not raw card data).

40

Therapist Rates

040_therapist_rates.sql

Creates therapist_rates table with RLS. Stores per-therapist rate cards (standard, check-in, intake, group, crisis) synced to localStorage.

41

Client Rates

041_client_rates.sql

Creates client_rates table with RLS. Per-client rate overrides so therapists can set custom rates for individual clients.

42

Calendar Event Rate

042_calendar_event_rate.sql

Adds rate NUMERIC and rate_key TEXT columns to calendar_events. Enables per-event rate tracking and auto-invoice generation on session completion.

43

Invoice Stripe Fields

043_invoice_stripe_fields.sql

Adds Stripe payment tracking fields to invoices and therapist profiles. Enables Stripe Checkout and Connect Express payouts.

44

Insurance Fields

044_insurance_fields.sql

Insurance and clinical billing fields for superbill generation. Supports out-of-network reimbursement workflow.

45

NPI Billing Fields

045_npi_billing_fields.sql

Therapist billing identity fields (EIN, practice address) required on superbills.

46

Subscriptions

046_subscriptions.sql

SaaS subscription system — adds subscription tier/status columns to profiles and creates ai_usage_log table for AI usage tracking.

47

Chime Migration

047_chime_migration.sql

Adds meeting_id and transcription_source columns to session_transcripts for AWS Chime SDK + Deepgram migration.

48

Journal Entry Analyses

048_journal_entry_analyses.sql

Persists AI journal analysis results in Supabase. One analysis per entry via upsert; re-analysis overwrites previous result. RLS-protected.

49

Journal Enhancements

049_journal_enhancements.sql

Adds prompt_text, parent_entry_id to entries. Creates journal_entry_links table. Adds highlight positions to journal_notes.

50

Group Sessions

050_group_sessions.sql

Creates group_sessions table for therapist group debriefs. Participants and reflections stored as JSONB. RLS scoped to owning therapist.

51

Client Day Notes & Notification Prefs

051_client_day_notes_and_notif_prefs.sql

Creates client_day_notes (one row per user/date). Adds notification_prefs JSONB to profiles. Replaces localStorage for cross-device persistence.

52

Journal Entry Labels

052_journal_entry_labels.sql

Adds labels TEXT[] to journal_entries for persistent entry tagging. Inherits existing RLS policies.

53

Messaging Realtime Fix

053_direct_messages_realtime_fix.sql

Sets REPLICA IDENTITY FULL on direct_messages so Supabase Realtime column-filter subscriptions (to_user_id=eq.<uuid>) fire correctly from WAL events.

54

Message PHI Audit Log

054_message_phi_audit.sql

HIPAA §164.312(b) — immutable message_audit_log table. Triggers log every message send and read receipt. Service-role-only RLS. Admin RPC exposes aggregate stats.

55

Directory Visibility Toggle

055_directory_visibility.sql

Adds directory_visible BOOLEAN DEFAULT false to profiles. Opt-in toggle for clients to appear in the community directory. Therapists are always visible regardless.

56

Therapist-Client Request RPCs

056_therapist_client_request_rpcs.sql

Adds get_incoming_tc_requests, get_sent_tc_requests, accept_tc_request, decline_tc_request, cancel_tc_request, and request_therapist_connection — replaces peer-friendship RPCs with therapist_clients-aware equivalents. Validates target role server-side.

57

Public Profile Page & Chime Meeting ID

057_profile_public_page_and_chime_meeting.sql

Adds chime_meeting_id to calendar_events for idempotent meeting joins. Adds public profile fields to profiles: public_page_headline, public_page_calendly_url, public_page_website, public_page_links (JSONB), public_page_accepting_clients.

58

Profiles Public Read & Insurance Portal

058_profiles_public_read_and_insurance.sql

Adds profiles_authenticated_read RLS policy so authenticated users can view other profiles (directory, connect, clinician-profile). Adds insurance_portal_url column to profiles.

59

Therapist Clients Request Note

059_therapist_clients_request_note.sql

Adds request_note column to therapist_clients for intro messages with connection requests. Updates get_incoming_tc_requests and get_sent_tc_requests RPCs to return the note.

60

Therapist-Initiated Connections

060_therapist_initiate_connection.sql

Lets therapists initiate a connection request to a client (reverse of the existing client-initiated flow). Adds companion RPCs so both directions flow through therapist_clients with proper status handling.

61

Clinician Reviews & Profile v2

061_clinician_reviews_and_profile_v2.sql

Adds clinician_reviews table with RLS (connected clients only, 7-day edit window, clinician flag-only guard). Enriches profiles with approaches, populations_served, languages, insurance_accepted, accepts_self_pay, license_state, plus availability/rate visibility toggles. Publishes get_clinician_review_stats and get_clinician_public_availability RPCs.

62

Message Soft Delete & Retract

062_message_soft_delete.sql

Adds per-side hidden_from_sender_at / hidden_from_recipient_at to direct_messages so participants can clear their own view without destroying the audit row. Adds sender-only retract (content replaced with tombstone while unread; original_content preserved) via retract_direct_message() RPC. Rewrites SELECT RLS so hidden rows are invisible to the hider, and splits UPDATE policies + guard trigger to enforce column-level access.

63

Message Retract v2 (Two-Sided Tombstone)

063_message_retract_v2.sql

Extends retract so senders can take a message back even after it's been read — both sides see [message retracted]. Adds retracted_by column and 'retracted' event type in message_audit_log for §164.312(b) audit clarity. Relaxes the 062 guard trigger to permit post-read retracts while keeping column-level immutability on from/to/created_at/original_content.

64

Calendar Event Kind

064_calendar_event_kind.sql

Adds event_kind column to calendar_events ('session' or 'event', default 'event'). Lets calendar UIs distinguish video sessions from regular appointments so only session-kind events trigger the participant RSVP picker.

65

Session Invites (RSVP)

065_session_invites.sql

New session_invites table tracking invite lifecycle (pending → accepted/declined/cancelled) for both calendar sessions and ad-hoc lobby invites. RLS lets only inviter and invitee read; mutations go through rsvp_session_invite and cancel_session_invite SECURITY DEFINER RPCs that enforce role-based transitions.

66

Clinical Frameworks

066_clinical_frameworks.sql

Adds clinical_framework column to profiles (CBT/IFS/EFT/Polyvagal). Tailors how ai-chat and ai-analyze edge functions interpret session and journal data — surfacing distortions for CBT, parts for IFS, attachment cycles for EFT, and autonomic state for Polyvagal.

67

Connection Request Note Parameter

067_connection_request_note_param.sql

Extends request_therapist_connection() and request_client_connection() to accept an optional request_note via a default-NULL second parameter. Previous callers continue to work unchanged. Closes the gap where the therapist_clients.request_note column existed (mig 059) but no RPC actually wrote to it.

68

Whiteboards / Boards Canvas

068_whiteboards.sql

Five-table set powering the Boards canvas: whiteboards, whiteboard_items, whiteboard_shares, whiteboard_audit_log, and whiteboard_ai_runs. Owner-only by default with explicit per-user shares; every mutation logs to the §164.312(b) audit table via trigger.

69

Assignment Submission Review

069_assignment_review.sql

Adds inline-review capability for assignment submissions: assignment_annotations (pen/highlight/arrow/rect/text overlays), assignment_comments (anchored or threaded), and assignment_feedback_messages (chat-style thread). Coordinates use fractional (0..1) page units so markups survive viewer resizes; all three tables join the realtime publication.

70

Journal Notes — AI Annotations

070_journal_notes_ai_annotations.sql

Extends journal_notes with is_ai_generated, internal_label (CBT taxonomy code), and severity (1–3). Clients see a warm comment-icon UX; the internal label powers therapist clinical view and pattern-frequency analytics but is never surfaced to the client.

71

Journal Insights (per-entry)

071_journal_insights.sql

New journal_insights table — one row per (entry, engine_version) storing the linguistic engine's output: emotion vector (joy/sadness/anxiety/anger/shame/hope), Burns's 10 canonical distortion codes with example quotes, pattern IDs, word/question counts, and reflection score. Idempotent RLS pattern with DROP-then-CREATE so the migration can be safely re-run.

72

Journal RLS — Active Connection

072_journal_rls_active_link.sql

Plugs Security Audit Critical #2 — the old policies keyed journal read access off the existence of a client_notes row, which is never cleaned up when a therapeutic relationship ends. Replaces the policies on journal_entries and journal_insights with an EXISTS check against therapist_clients.status = 'active'. Read access disappears the instant the relationship is paused or ended.

73

Directory Visibility — Backfill

073_directory_visibility_backfill.sql

Backfills every existing therapist's directory_visible to true so the tightened directory filter (which now honors the flag for every role, not just clients) doesn't suddenly hide active practices. Column default stays false for new signups — HIPAA-safe default, opt-in via Settings → Privacy.

74

Role Refactor: clinician_type + supervision + audit

074_role_refactor_supervision_audit.sql

Phase 0 of the Associate + Consultation rollout. Adds clinician_type ('licensed' | 'associate'), license_kind, registration_number, and license_expires_on to profiles; creates supervision_relationships with status lifecycle RPCs (invite_supervisor, accept_supervision, decline_supervision, end_supervision); generalized audit_log + log_audit_event(); seeded jurisdiction_rules for CA, NY, TX, FL, IL.

75

Disclosures + Supervision Sessions

075_disclosures_supervision_sessions.sql

Adds disclosures_acknowledged (record-of-consent that an associate's pre-licensed status was shown to the client) and supervision_sessions (formal hour-logging — immutable after both parties sign). sign_supervision_session() RPC handles co-signing.

76

session_notes Co-sign + note_type

076_session_notes_cosign.sql

Adds note_type ('progress' | 'psychotherapy'), client_id, and the full co-sign column set (requires_cosign, cosigned_by, cosigned_at, cosign_status) to session_notes. Trigger auto-flags associate-authored notes as requiring cosign. New note_comments table for inline review. approve_session_note() + request_note_changes() RPCs.

77

Anonymous Client References (ACR)

077_anonymous_client_refs.sql

Keystone of de-identification-by-design for consultation. Clinicians never paste raw client info — they anchor a thread to an ACR pseudonym ("Client A"). The real_client_id ↔ acr mapping is RLS-locked to the owning clinician; non-owners can only fetch the pseudonym via get_acr_pseudonym().

78

Consultation Rooms + Members + Join Requests

078_consultation_rooms.sql

Container schema for Therapist-to-Therapist Consultation. consultation_rooms (standing groups + adhoc consults), consultation_room_members (owner/member/observer roles), consultation_join_requests. RLS fails closed for non-therapists at every level. RPCs: create_standing_consult, create_adhoc_consult, approve_consult_join_request, decline_consult_join_request.

79

Consultation Messages + Sessions

079_consultation_messages_sessions.sql

Interaction layer: consultation_messages (realtime publication, soft-delete only, optional ACR anchor, PHI scan results stored alongside body), and consultation_sessions (scheduled video calls — recording is opt-in per session and OFF by default). Schema enforces: no foreign key from messages to session_notes, so psychotherapy notes can never be referenced.

80

Certifications (Documentation Layer)

080_certifications_layer.sql

LinkedIn-style credentials surface for every clinician — primary credibility for Life Coaches (NLP, ICF, etc.); secondary credentials for licensed/associate clinicians (EMDR, IFS, modality-specific training). New certifications table with public read + owner CRUD + admin verify/unverify RPCs. Private certifications Storage bucket holds supporting documents (5-minute signed URLs on admin review). Also extends clinician_type CHECK to formally allow 'life_coach'.

81

Session Titles

081_session_titles.sql

Optional clinician-chosen title on session_transcripts so past sessions can be renamed beyond the default "Client · Date" label.

82

Journal Entry Shares + Review Hardening

082_journal_entry_shares.sql · 083 · 084 · 085_journal_self_annotations.sql

Four-migration wave that hardened the journal sharing flow: per-entry share grants (clinician-only access), explicit clinician review tracking, RLS hardening against share-leakage, and a private self_annotations store for the journal writer's own ink/notes (separate from clinician-visible journal text).

86

Associate Onboarding Hardening + Supervisor Read

086_associate_onboarding_hardening.sql · 087_supervisor_read_access.sql

Associate clinician onboarding flow + supervisor read access to their associates' clients (read-only). Combined with the existing co-sign flow this completes the supervision audit trail required by most state licensing boards.

88

Consultation Tier + Audit

088_consultation_tier_and_audit.sql · 089_auth_display_name_sync.sql

Tier-aware consultation room access (Solo Plus and up) and an audit log. Plus a trigger that keeps auth.users.raw_user_meta_data.display_name in sync with profiles.display_name so OAuth providers (Google) show the right name on re-auth.

90

Client Pattern Summary (Privacy-Preserving)

090_client_pattern_summary.sql

Aggregated 30-day signals (journal_insights + session_analysis) for one client surfaced to the therapist as 3-5 abstract trends with source dates. NEVER sends raw journal text to Claude — only emotion vectors, distortion codes, mood scores, theme arrays, dates. PHI-minimization patent territory.

91

Tier Restructure: Base → Solo Lite

091_rename_free_to_base.sql · 092_add_onboarded_column.sql · 093_tier_restructure_solo.sql · 094_ai_extra_credits.sql

Four-migration wave that normalized the subscription tier ladder. Renamed legacy free → base → lite across all rows + CHECK constraints. Added profiles.onboarded boolean (login flow gating). Added ai_messages_extra column + add_ai_credits RPC for purchased top-up packs (100 / 250 actions).

95

Enterprise Practices (Multi-Seat)

095_practices.sql · 096_practice_invitations.sql

Enterprise tier infrastructure: practices + practice_members + credential_verifications tables, is_practice_admin RPC, create_practice RPC, plus practice_invitations with token-based invite acceptance. Per-practice subscription tier (practice / practice_plus / practice_pro) with pooled AI quota + seat limits.

97

Test-Bypass Flag + Guards

097_test_bypass_flag.sql · 098 · 099 · 100 · 101_create_practice_explicit_user.sql

Five-migration wave for safe dev-account testing: profiles.is_test_bypass column (NOT user-writable via RLS), apply_test_bypass SECURITY DEFINER RPC, app.bypass_profile_guard session-local config check, and a refactor of create_practice to take an explicit p_user_id (so service-role callers don't get NULL from auth.uid()).

102

Practice Team Stats RPCs

102_practice_team_stats.sql

get_practice_team_overview + get_practice_member_detail RPCs powering the Practice Team monitoring page. PHI-minimized aggregates only (client initials, theme arrays — no raw notes). Tightens create_practice_invitation so admin role can only be invited by the practice owner (not other admins).

103

In-App Notifications

103_notifications.sql

Universal in-app notifications table (type + jsonb metadata so future notification kinds drop in without schema changes). RLS: users see/update only their own. INSERTs only via SECURITY DEFINER RPCs. Hooks into create_practice_invitation so existing-user invites get an in-app notification in addition to the email.

104

Effective Subscription Resolution

104_effective_subscription.sql

Two RPCs that resolve a user's "effective" subscription — the practice row if they're an active practice_member, else their own profile. Fixes the bug where invited Enterprise clinicians were blocked from AI features because their personal subscription_tier defaulted to 'lite'. Upgrades the existing increment_ai_usage helper to route quota increments to the correct row (practice vs profile).

105

Pattern Memory Foundation (F1 · F2 · F3)

105_pattern_memory_foundation.sql

Three foundation tables that unlock the entire pattern-memory feature layer: entity_mentions (every reference to a person/role across journals + sessions, with canonical IDs so "mom" / "mama" / "mother" link to the same entity), topic_observations (per-utterance topic tags from the browser tagger), and client_baselines (per-user running statistics — EWMA mean+variance for numeric metrics, histograms for distributions). Includes update_client_baseline RPC with EWMA math built-in. Powers Lexical Register Tracker, Behavioral Signatures, Crisis Early Warning (n-of-1), and every other n-of-1 calibrated feature.

astrasera Control Center

Platform Stats

Database

External Services

Admin Actions

Messaging PHI Compliance

SQL Migrations