Database Schema & ORM

Relevant source files

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

• shared/src/schema.ts
• server/src/test/schema.test.ts
• shared/src/validators.ts
• server/src/routes/forum.ts
• server/src/routes/admin.ts
• server/src/routes/payments.ts
• server/src/routes/users.ts
• client/src/types/index.ts

SoulSeer utilizes a PostgreSQL database managed via Drizzle ORM, providing a type-safe interface between the Node.js backend and the data layer. The schema is centralized in a shared package to ensure consistency across the monorepo, allowing both the server and client to reference the same data structures.

The architecture follows a relational model optimized for a marketplace environment, supporting user roles (clients, readers, and admins), real-time balance tracking, and a community forum. The schema is defined using TypeScript, leveraging Drizzle's ability to infer types directly from table definitions.

Sources: README.md:16, shared/package.json:44, shared/src/types.ts:1-20

Core Data Architecture

The system is built around several key entities that facilitate spiritual consultations and financial transactions.

Entity Relationship Diagram

The following diagram illustrates the primary relationships between users, their readings, financial history, and forum interactions.

Key Enumerations

The schema employs several TypeScript enums (represented as PostgreSQL enums) to enforce data integrity across the platform.

Enum Name	Values	Context
UserRole	client, reader, admin	Authorization and profile capabilities.
ReadingType	chat, voice, video	Modalities of spiritual consultation.
ReadingStatus	pending, accepted, in_progress, completed, cancelled	Lifecycle of a reading session.
TransactionType	topup, reading_charge, reader_payout, refund, admin_adjustment	Financial ledger categories.

Sources: server/src/test/schema.test.ts:59-86, shared/src/validators.ts:16-20, client/src/types/index.ts:50-84

Data Models

Users Table

The users table serves as the primary entity for all participants. It stores profile information, authentication IDs from Auth0, and financial metadata for Stripe.

Field	Type	Description
id	Serial (PK)	Unique identifier for the user.
auth0Id	String	Link to Auth0 authentication provider.
email	String	User's unique email address.
role	Enum	Determines platform permissions.
balance	Integer	Real-time account balance in cents.
isOnline	Boolean	Online presence status for readers.
pricingChat	Integer	Per-minute rate for chat sessions (cents).
stripeAccountId	String	Stripe Connect ID for reader payouts.

Sources: server/src/test/schema.test.ts:27-41, server/src/routes/users.ts:154-158

Readings Table

This table tracks consultation sessions between clients and readers, including pricing and transcripts.

Field	Type	Description
id	Serial (PK)	Unique session identifier.
clientId	Integer (FK)	Reference to the user acting as client.
readerId	Integer (FK)	Reference to the user acting as reader.
ratePerMinute	Integer	Rate locked at the start of the session.
totalCharged	Integer	Final cost billed to the client.
chatTranscript	JSON	Array of messages exchanged during session.
rating	Integer	Client-provided score (1-5).

Sources: server/src/test/schema.test.ts:43-57, shared/src/validators.ts:81-85

Transactional Logic & Integrity

The ORM ensures financial integrity through ACID transactions, particularly during balance adjustments and payouts.

Balance Adjustment Flow

When an admin manually adjusts a user's balance, the system wraps the user update and the transaction log entry into a single atomic operation.

Payout Implementation

The reader payout process involves a critical sequence where external Stripe transfers must succeed before the internal database balance is zeroed.

1. Validation: Check if reader has minimum balance ($15.00).
2. External Call: Execute stripe.transfers.create.
3. Database Update: Use an optimistic lock to update user balance to 0.
4. Audit Log: Insert a reader_payout record in the transactions table.

Sources: server/src/routes/admin.ts:333-395

Community & Forum Schema

The forum system consists of three primary tables: forumPosts, forumComments, and forumFlags.

Content Moderation Relationships

• forumPosts: Stores top-level discussions, categorized by interest (e.g., "General", "Readings").
• forumComments: Stores threaded replies linked to a postId.
• forumFlags: Stores reports against content for moderation, including a resolved boolean status.

Sources: server/src/test/schema.test.ts:54-57, server/src/routes/forum.ts:16-25

Summary

The Database Schema & ORM layer provides the backbone for SoulSeer’s business logic. By utilizing Drizzle ORM and a shared schema, the project maintains high data integrity for complex per-minute billing and financial auditing while providing a consistent developer experience across the server and client applications.