Ideon Logo
Ideon

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, displayName, avatarUrl, color.
    • role: System role (user or admin).
    • lastOnline: Timestamp of last activity.
    • invitedByUserId: Reference to the user who invited them.
  • sessions: Manages active user sessions (NextAuth).
  • emailVerifications: Stores verification codes for email confirmation.
  • passwordResets: Stores tokens for password recovery.
  • magicLinks: Tokens for passwordless login.
  • invitations: Pending invitations for new users.
  • rateLimits: Stores rate limiting data (key, points, expiry) for production.
  • userGitTokens: Encrypted Personal Access Tokens for Git providers.
    • provider: github, gitlab, etc.
    • host: github.com or self-hosted domain.
    • token: AES-256-GCM encrypted token.

Workspace & Projects

  • folders: Containers for projects.
    • name: Folder name.
    • ownerId: User who owns the folder.
  • folderCollaborators: Access control for folders.
    • role: editor or viewer.
    • Inherits access to all projects inside.
  • projects: The central entity.
    • folderId: Optional reference to parent folder.
    • currentStateId: Pointer to current state (snapshot).
  • projectCollaborators: Direct access to projects.
    • role: owner, editor, viewer.
  • projectRequests: Pending access requests from users.
    • status: pending, rejected.
  • systemSettings: Global instance configuration.

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, sketch.
    • metadata: JSON blob for block-specific properties (e.g. dimensions, colors).
    • data: JSON blob for content/state (e.g. text content, file URL).
    • positionX, positionY: Canvas coordinates.
  • blockReactions: User emoji reactions on blocks.
    • blockId, userId, emoji.
  • linkPreviews: Cached OpenGraph metadata for Link blocks.
    • url, title, description, imageUrl.
  • links: Edges between blocks.
    • source, target: IDs of connected blocks.
    • sourceHandle, targetHandle: Connection points.
    • label: Optional text label on the connection line.
    • animated: Visual style flag.
  • temporalStates: Stores the history of operations (Undo/Redo).
    • 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
    • folders, folderCollaborators
    • projectRequests

Enterprise SSO (SAML)

These tables are managed by BoxyHQ SAML Jackson library for handling SAML 2.0 flows.

  • jackson_store: Key-value store for SAML config and sessions.
  • jackson_index: Secondary indices for faster lookups.
  • jackson_ttl: Time-to-live management for ephemeral data.

GitHub Integration

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