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 organizing projects.
  • name: Folder name.
  • ownerId: User who owns the folder.
  • isStarred: Boolean flag (0 or 1) for favorite status.
  • deletedAt: Soft-deletion timestamp (nullable).
  • createdAt, updatedAt: Timestamps.
• folderCollaborators: Access control for folders.
  • folderId, userId: Primary key.
  • role: owner, admin, editor, viewer.
  • Inherits access to all projects inside.
  • createdAt: When access was granted.
• projects: The central entity for workspaces.
  • name, description: Project metadata.
  • ownerId: Project creator.
  • folderId: Optional reference to parent folder.
  • currentStateId: Pointer to current snapshot.
  • shareToken: Secure token for public sharing.
  • shareEnabled: Boolean flag (0 or 1) to enable/disable public access.
  • shareCreatedAt: When sharing was first enabled.
  • deletedAt: Soft-deletion timestamp (nullable).
  • acceptedAt: When shared project was accepted by receiver (nullable).
  • createdAt, updatedAt, lastOpenedAt: Timestamps.
• projectStars: Marks projects as favorites.
  • projectId, userId: Primary key.
  • createdAt: When starred.
• projectCollaborators: Direct access control for projects.
  • projectId, userId: Primary key.
  • role: owner, editor, viewer.
  • createdAt: When access was granted.
• projectRequests: Pending access requests from users.
  • projectId, userId: Linked to project and requesting user.
  • status: pending or rejected.
  • createdAt: When request was made.
• systemSettings: Global instance configuration.
  • installed: Boolean flag (0 or 1) for setup completion.
  • publicRegistrationEnabled: Toggle for self-registration.
  • ssoRegistrationEnabled: Toggle for SSO registration.
  • passwordLoginEnabled: Toggle for password authentication.
  • authProvidersJson: JSON config for OAuth providers.
  • createdAt: When settings were initialized.

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, shell, kanban (13 types, indexed 0-12).
  • metadata: JSON blob for block-specific properties (e.g., Kanban columns, Sketch data).
  • content: Text content (nullable).
  • data: JSON blob for rendering state.
  • positionX, positionY: Canvas coordinates.
  • width, height: Block dimensions (nullable, user-resizable).
  • ownerId: User who owns/created the block.
  • parentBlockId: Optional reference for nested blocks.
  • selected: Boolean flag for selection state (0 or 1).
  • createdAt, updatedAt: Timestamps.
• blockSnapshots: Historical snapshots of block state for recovery.
  • blockId, content, data, metadata.
• blockReactions: User emoji reactions on blocks.
  • blockId, userId, emoji, createdAt.
• linkPreviews: Cached OpenGraph metadata for Link blocks.
  • url, title, description, imageUrl, fetchedAt.
• links: Edges between blocks.
  • source, target: IDs of connected blocks.
  • sourceHandle, targetHandle: Connection point identifiers.
  • type: Connection type (nullable).
  • label: Optional text label on the connection line.
  • animated: Visual style flag (0 or 1).
  • sourceX, sourceY, targetX, targetY: Normalized connection coordinates.
  • sourceOrientation, targetOrientation: Connection orientation hints.
  • data: JSON blob for advanced link metadata.
  • createdAt, updatedAt: Timestamps.
• temporalStates: Stores the history of operations (Undo/Redo) and snapshots.
  • projectId, parentId: Linked list structure for history tree.
  • authorId: User who made the change.
  • intent: Description of the action (e.g., "Create block", "Move block").
  • diff: JSON patch describing the change.
  • isSnapshot: Boolean flag (0 or 1) for full state checkpoints.
  • isAuto: Boolean flag (0 or 1) for automatic snapshots (e.g., after significant actions).
  • timestamp: When the change was recorded.

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

Git Integration

• userGitTokens: Encrypted Personal Access Tokens for Git providers.
  • userId: Owner of the token.
  • provider: Provider type (e.g., github, gitlab, gitea, forgejo).
  • host: Repository host domain or self-hosted instance (e.g., github.com, gitlab.corp.local).
  • token: AES-256-GCM encrypted token.
  • enabled: Boolean flag (0 or 1) to enable/disable the token.
  • createdAt, updatedAt: Timestamps.
• githubRepoStats: Caches repository statistics to avoid API rate limits.
  • url: Repository URL.
  • owner, repo: Parsed owner and repository name.
  • data: JSON blob containing stars, releases, commits, issues, and contributor count.
  • fetchedAt: When stats were last updated.