Ideon Logo
Ideon
GitHub

Database Schema

Ideon supports both PostgreSQL (production) and SQLite (development). The schema is managed via Kysely migrations and is compatible with both dialects.

Core Tables

users

Stores user accounts.

  • id: UUID (Primary Key)
  • email: Unique email address
  • username: Unique username
  • passwordHash: Argon2 hash (nullable for SSO users)
  • role: 'superadmin' | 'admin' | 'member'
  • displayName, avatarUrl, color: Profile info

projects

Stores project metadata.

  • id: UUID
  • name: Project name
  • description: Optional description
  • ownerId: References users.id
  • currentStateId: References the current temporalState

blocks

Stores the visual elements on the canvas.

  • id: UUID
  • projectId: References projects.id
  • blockType: 'text', 'link', 'core', 'snippet', 'github', 'file', 'palette', 'contact', 'video', 'checklist'
  • content: Primary text content
  • data: JSON blob for block-specific data (e.g., isLocked, ownerId, specific properties)
  • positionX, positionY, width, height: Spatial coordinates

links

Stores connections between blocks.

  • id: UUID
  • source: ID of source block
  • target: ID of target block
  • sourceHandle, targetHandle: Specific connection points

temporalStates

Stores the history/snapshots of projects (Versioning).

  • id: UUID
  • projectId: References projects.id
  • intent: Description of the change (commit message)
  • diff: JSON patch or full snapshot of changes
  • isSnapshot: Boolean (major version vs incremental)
  • authorId: References users.id

projectCollaborators

Manages project access.

  • projectId: References projects.id
  • userId: References users.id
  • role: 'editor' (default)

systemSettings

Global instance configuration.

  • installed: Installation status flag
  • publicRegistrationEnabled: Boolean (0 or 1)
  • authProvidersJson: Configuration for enabled SSO providers

auditLogs

Security and activity logging.

  • id: UUID
  • userId: Actor
  • action: Event type (e.g., 'login', 'project_create')
  • ipAddress: Request origin
  • status: 'success' | 'failure'

githubRepoStats

Cache for GitHub repository data to reduce API calls.

  • url: Repo URL
  • data: Cached JSON stats (stars, forks, etc.)
  • fetchedAt: Timestamp