Database Schema & Drizzle ORM

Relevant source files

The following files were used as context for generating this wiki page:

• replit.md
• lib/db/src/schema/conversations.ts (Referenced via security pass instructions)
• lib/db/src/schema/supportRequests.ts (Referenced via support flow instructions)
• artifacts/api-server/src/routes/anthropic/index.ts
• lib/db/src/schema/index.ts (Referenced as export point for supportRequests)

The database layer for the Hospice Roadmap project is built using PostgreSQL and managed through Drizzle ORM. This architecture provides a type-safe, relational data model that supports the application's core features, including AI-driven conversations (Ragna), user support requests, and patient context persistence. The schema is defined in TypeScript, allowing for seamless integration between the database and the Express API server.

The database is primarily used to manage session-based interactions. While the mobile application utilizes AsyncStorage for local persistence of symptom logs and journals, the server-side database handles persistent conversation threads, message history for the Ragna AI companion, and formal support submissions.

Sources: replit.md, attached_assets/Pasted-Make-a-focused-security-pass-on-the-Ragna-conversation-_1774838250254.txt

Core Data Models

The database schema is divided into distinct modules for conversations and support interactions. Each table is designed with device-scoped ownership to ensure privacy without requiring a full user authentication system.

Conversation & Message Schema

The conversation system tracks interactions between the user and the Ragna AI. It uses a one-to-many relationship between conversations and messages.

• Device Scoping: Every conversation is tied to a client_id (persisted on the mobile device via AsyncStorage).
• Persistence: Messages are stored with their role (user vs. assistant) to allow for cross-session memory and context injection.

The diagram shows the relationship between conversation metadata and the actual message content exchanged with the AI.
Sources: lib/db/src/schema/conversations.ts, artifacts/api-server/src/routes/anthropic/index.ts:31-110

Support Request Schema

The support_requests table manages formal inquiries submitted via the app's support flow. Unlike the conversation history, these records are intended for administrative review and triage.

Field	Type	Description
id	serial	Primary key
client_id	text	Device-specific identifier (Default: "anonymous_support")
name	text	User's full name
email	text	User's contact email (validated on server)
phone	text	Optional contact number (normalized to null)
topic	text	Subject of the inquiry
preferred_contact	text	"email" or "phone"
message	text	Detailed request (min. 10 characters)
status	text	Default: "received"
source	text	Default: "ios_app"

Sources: lib/db/src/schema/supportRequests.ts, artifacts/api-server/src/routes/anthropic/index.ts:18-20

Client-Based Security Architecture

The project implements "device scoped ownership." This ensures that a mobile client can only access data (conversations and messages) that matches its unique, persistent x_client_id header.

This sequence illustrates the server-side enforcement of data ownership based on the client identity.
Sources: artifacts/api-server/src/routes/anthropic/index.ts:13-30, attached_assets/Pasted-Make-a-focused-security-pass-on-the-Ragna-conversation-_1774838250254.txt

Key Implementation Details

Client ID Validation

The server uses a strict regex to validate incoming client identities to prevent unauthorized database access or injection. The format follows ^client_[a-z0-9_]+$.

// artifacts/api-server/src/routes/anthropic/index.ts:13-25
const CLIENT_ID_REGEX = /^client_[a-z0-9_]+$/;

function requireClientId(req: Request, res: Response): string | null {
  const raw = req.header("x_client_id");
  const clientId = raw?.trim() ?? "";
  if (!clientId || !CLIENT_ID_REGEX.test(clientId)) {
    res.status(401).json({ error: "Missing or invalid client identity" });
    return null;
  }
  return clientId;
}

Sources: artifacts/api-server/src/routes/anthropic/index.ts:13-25

Schema Exports and Registry

Drizzle ORM tables are centralized in lib/db/src/schema/index.ts. This allows the API server to import all database entities from a single entry point using @workspace/db.

• Tables: conversations, messages, support_requests.
• ORval Integration: The OpenAPI spec utilizes these schemas to generate frontend Zod validators and React Query hooks.

Sources: lib/db/src/schema/index.ts, replit.md

Conclusion

The database architecture of the Hospice Roadmap project prioritizes data integrity and client privacy through a relational Drizzle ORM setup. By linking all major entities (conversations and support requests) to a persistent client identity, the system provides a personalized experience without the friction of a traditional account registration, while maintaining a clear audit trail for the hospice intelligence engine's memory synthesis and support triage.