Technical Specification: Multi-Tenant Architecture & Context Strategy

Technical Specification: Multi-Tenant Architecture & Context Strategy

1. Executive Summary

This document defines the infrastructure for the Oracore platform. We are adopting a Unified Tenant Model to handle diverse user types (Students, Solo Travelers, Private Practices, and DSOs) within a single database schema.

The Core Philosophy:

• Every User belongs to a Location context.
• For Fixed Users (Offices), the Location is physical.
• For Mobile Users (Students/Travelers), the Location is logical (a "Personal Container").
• Beta Constraint: The UI will not require location selection. The backend will infer context based on a default_location_id.

2. Core ERD (Entity Relationship Diagram)

We will use a rigid Institution > Location > User hierarchy. We will not create separate tables for "Solo" vs "Enterprise" users.

A. Core Tables

1. Institutions (The Billing Entity)

• id: UUID
• name: String (e.g., "University of Colorado", "Dr. Smith LLC", "Heartland Dental")
• type: Enum (academic, private_practice, dso)

2. Locations (The Data Container)

• id: UUID
• institution_id: FK -> Institutions.id
• name: String (e.g., "Downtown Clinic" OR "John Doe Student Log")
• is_virtual: Boolean (True for Students/Travelers; False for Physical Offices)

3. Users (The Identity)

• id: UUID
• email: String
• default_location_id: FK -> Locations.id (CRITICAL FOR BETA)

4. Organization_Members (The Many-to-Many Glue)

• user_id: FK -> Users.id
• location_id: FK -> Locations.id
• role: Enum (owner, provider, staff, student)

5. Visits (The Operational Data)

• id: UUID
• location_id: FK -> Locations.id (Data Ownership)
• provider_user_id: FK -> Users.id (Attribution)
• remote_facility_name: String (Optional. Used by students/travelers to note where they were).

3. The Beta Implementation Strategy

For Beta, we are simulating a "Single Tenant" experience for the user, even though the database is "Multi-Tenant."

The "Auto-Context" Middleware Rule

Since we do not have a location selector in the UI yet, the Backend must handle context resolution automatically.

Logic Flow:

1. Frontend Request: Sends POST /visits with body data (Patient Name, Procedure, etc.). No Location ID is sent.
2. Auth Middleware: Authenticates User via Token.
3. Context Injection:

• Look up user.default_location_id.
• Inject this ID into the request body/context as location_id.

1. Database Write: The record is saved with the correct location_id.

The User Setup Scenarios (Backend Logic)

| User Type | Setup Action (Onboarding) | Resulting Data Structure |
|-----------|---------------------------|--------------------------|
| Student | Create Inst: "Univ X"<br>Create Loc: "Student Logbook"<br>Set Default Loc: "Student Logbook" | User actions save to their private logical location. |
| Solo Traveler | Create Inst: "Traveler LLC"<br>Create Loc: "Mobile Ops"<br>Set Default Loc: "Mobile Ops" | User actions save to their private logical location. |
| Office Staff | Invite to Inst: "Existing Practice"<br>Link to Loc: "Main Office"<br>Set Default Loc: "Main Office" | User actions save to the shared physical office. |
| DSO Employee | Link to Locs: "North", "South"<br>Set Default Loc: "North" | For Beta, user only sees/adds data to their Primary ("North") location. |

4. Query Logic (Access Control)

To ensure users only see what they are supposed to see, we apply filters based on Location Context, not just User ID.

General View (Dashboard/List):

SELECT * FROM visits
WHERE location_id = [user.default_location_id];

Why this works:

• Solo User: Gets their private log.
• Office User: Gets the whole office's shared log (collaborative view).

Permissions Layer (Optional for Beta):

• If role == student OR solo_provider: Can DELETE own records.
• If role == staff: Cannot DELETE.

5. Future Scalability (V2 Roadmap)

This architecture allows us to move to V2 (Multi-Location Support) without database migration.

Transition Plan:

1. Frontend Update: Build a "Location Selector" dropdown in the header.
2. API Update: Update the Middleware to look for a header x-context-location-id.
3. Logic: location_id = header_id OR user.default_location_id

Reporting:
To build a DSO Dashboard, we simply query:

SELECT * FROM visits WHERE institution_id = [X]

(Aggregating all child locations).

6. Development Checklist

• [ ] Database: Ensure Users table has default_location_id.
• [ ] Database: Ensure Visits table requires location_id.
• [ ] Onboarding API: Write logic to auto-generate a "Logical Location" for Solo/Student signups.
• [ ] Middleware: Implement the "Context Injection" logic to read the default location.
• [ ] Frontend: Build views assuming a single data stream (no location toggle needed yet).