Ideon Logo
Ideon
GitHub

Database Architecture

Ideon uses Kysely as a query builder and migration manager. The database schema is designed to support real-time collaboration, temporal state management (undo/redo), and role-based access control.

Core Entities

Users & Authentication

  • users: Stores user profiles (email, username, avatar, password hash).
  • sessions: Manages active user sessions.
  • emailVerifications: Stores verification codes for email confirmation.
  • passwordResets: Stores tokens for password recovery.
  • magicLinks: specific tokens for passwordless login.
  • invitations: Pending invitations for new users.
  • rateLimits: Stores rate limiting data (key, points, expiry) for production.

Workspace & Projects

  • projects: The central entity. Contains metadata (name, description) and points to the current state.
  • projectCollaborators: Junction table linking users to projects with roles (owner, editor, viewer).
  • systemSettings: Global instance configuration (public registration, installed flag).

Project Graph (The "OS")

Ideon treats a project as a graph of blocks and links.

  • blocks: Nodes in the graph.
    • blockType: text, link, file, core, github, palette, contact, video, snippet, checklist.
    • metadata: JSON blob for block-specific properties.
    • data: JSON blob for content/state.
    • positionX, positionY: Canvas coordinates.
  • links: Edges between blocks.
    • source, target: IDs of connected blocks.
    • sourceHandle, targetHandle: Connection points.
    • animated: Visual style flag.
  • blockSnapshots: Version history for individual blocks.

Temporal State (Undo/Redo)

  • temporalStates: Stores the history of operations.
    • intent: Description of the action (e.g., "Move Block").
    • diff: JSON patch describing the change.
    • isSnapshot: Boolean flag for full state checkpoints.

Security & Audit

  • auditLogs: Records security-critical actions (login, settings change, project deletion).
  • Row Level Security (RLS): If running on PostgreSQL, RLS is enabled on sensitive tables (projects, blocks, links, auditLogs) to enforce access control at the database layer.

GitHub Integration

  • githubRepoStats: Caches repository statistics to avoid API rate limits.