BUG: Multiple database integrity issues in schema.sql missing therapy_type table, no unique constraint on assessment_results, and no ON DELETE rules on foreign keys

[Varadraj75]

Is there an existing issue for this?

• I have searched the existing issues

What happened?

📌 Issue Overview

supabase/schemas/schema.sql has three related database integrity problems that together make the schema non-executable from scratch, allow duplicate medical records, and leave orphaned rows on user deletion.

🔍 Steps to Reproduce

Issue 1 — Missing therapy_type table:

1. Run schema.sql against a fresh Postgres/Supabase instance.
2. Observe: ERROR: relation "therapy_type" does not exist on therapy_goal creation.

Issue 2 — Duplicate assessment submissions:

1. Open the assessment submission screen on a slow connection.
2. Tap submit twice before the response returns.
3. Two rows now exist in assessment_results for the same patient_id + assessment_id.

Issue 3 — No ON DELETE behavior:

1. Insert a patient with linked sessions, activity logs, and assessment results.
2. Attempt to delete the patient record.
3. Observe either a FK violation error or orphaned rows left in child tables.

🎯 Expected Behavior

• Schema should be executable from scratch without errors.
• Each patient should have at most one result per assessment.
• Deleting a patient should cascade to their owned data; deleting a therapist should set therapist_id to NULL in related records.

🚨 Actual Behavior

• therapy_goal references therapy_type(id) which does not exist in the schema.
• Duplicate assessment results can be silently inserted into medical records.
• No ON DELETE rules defined anywhere — orphaned data or blocked deletions.

📷 Screenshot

N/A

💡 Suggested Improvements

• Add CREATE TABLE therapy_type before therapy_goal in schema.sql.
• Add CONSTRAINT uq_patient_assessment UNIQUE (patient_id, assessment_id) to assessment_results.
• Add ON DELETE CASCADE or ON DELETE SET NULL to all foreign keys based on relationship semantics.
• Change .insert() to .upsert() with onConflict: 'patient_id,assessment_id' in the Edge Function.

Record

• I agree to follow this project's Code of Conduct
• I want to work on this issue