Database Schema & ORM

Relevant source files

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

• drizzle/meta/0001_snapshot.json
• README.md
• setup.sh
• src/app/api/workspaces/autogen/route.ts
• src/app/invite/link/[token]/layout.tsx

ThinkEx utilizes PostgreSQL as its primary relational database, managed through Drizzle ORM. The database architecture is designed to support a collaborative workspace environment where users can organize notes, media, and AI-driven insights. The schema is defined to handle user authentication, workspace management, and a persistent event-log system that tracks changes within individual workspaces.

The system emphasizes User-Controlled Context, ensuring that information remains persistent and organized across various media types such as PDFs, YouTube transcripts, and interactive quizzes.
Sources: README.md:55-58, drizzle/meta/0001_snapshot.json

Architecture and Core Components

The database layer is integrated into the Next.js framework using Drizzle ORM to provide type-safe queries and automated migrations.

Data Flow Overview

When a workspace is created (for instance, via the "Autogen" feature), the application logic performs a series of database operations:

1. Workspace Insertion: A new record is created in the workspaces table with a unique slug and sort order.
2. Event Logging: The system calls a stored procedure or function (append_workspace_event) to log the WORKSPACE_CREATED event.
3. Item Creation: Media items (PDFs, Images, Quizzes) are added as events or items linked to the workspace ID.

The diagram above illustrates the path from a user request to the persistent storage layer, highlighting the internal event logging mechanism.
Sources: src/app/api/workspaces/autogen/route.ts:246-302, setup.sh:317-320

Entity Relationship Model

The ThinkEx schema is structured around three main pillars: Identity (Auth), Workspaces, and the Event System.

Schema ER Diagram

This diagram represents the core relationships defined in the Drizzle snapshot, showing how users relate to workspaces and how workspaces aggregate content and collaboration data.
Sources: drizzle/meta/0001_snapshot.json

Core Tables Summary

Table Name	Primary Purpose	Key Fields
user	Stores primary identity and auth metadata.	id, email, is_anonymous
workspaces	The root container for user content.	id, slug, user_id, sort_order
workspace_events	Immutable log of all changes within a workspace.	event_id, event_type, payload, version
workspace_snapshots	Point-in-time states of a workspace to optimize loading.	snapshot_version, state
workspace_collaborators	Manages shared access and permissions.	workspace_id, user_id, permission_level

Sources: drizzle/meta/0001_snapshot.json

Detailed Data Models

Workspace Management

Workspaces use a slug for URL-friendly identification and a sort_order for UI positioning. They also store UI metadata such as icon and color themes.

// Example of workspace update logic
await db
  .update(workspaces)
  .set({ name: title, icon, color, slug: newSlug })
  .where(eq(workspaces.id, workspaceId));

Sources: src/app/api/workspaces/autogen/route.ts:468-472, drizzle/meta/0001_snapshot.json:1016-1070

Collaboration and Sharing

Sharing is handled via two distinct mechanisms:

1. Invites: Email-based invitations linked to specific users.
2. Share Links: Token-based access that allows collaborative joining via a URL.

Row Level Security (RLS) & Roles

The system is designed for a multi-tenant environment using PostgreSQL RLS (Row Level Security). The authenticated role is explicitly created during setup to manage session-based access.

• Service Schema: An auth schema is created containing a jwt() function that returns session data.
• Policies: Tables like user_profiles, workspace_events, and workspaces have policies that restrict SELECT, INSERT, and UPDATE operations to the owner (user_id) or verified collaborators.

Sources: setup.sh:285-300, drizzle/meta/0001_snapshot.json:344-380, 689-715

Database Initialization and Migrations

The project uses an interactive setup.sh script to manage the lifecycle of the database.

1. Docker Orchestration: Optionally runs PostgreSQL via Docker Compose.
2. Dependency Management: Uses pnpm to install Drizzle Kit.
3. Schema Push: Uses pnpm drizzle-kit push --force to synchronize the local TypeScript schema definitions with the physical database.
4. Stored Procedures: Initializes internal SQL functions such as append_workspace_event and auth.jwt().

Sources: setup.sh:215-240, 310-330, README.md:120-125

Conclusion

The ThinkEx Database Schema & ORM provides a robust foundation for spatial thinking. By leveraging Drizzle ORM's type-safety and a strictly versioned event-log architecture, the system ensures that AI reasoning and user notes remain consistently synchronized and secure across collaborative sessions.