Database Schema & ORM
Relevant source files
The following files were used as context for generating this wiki page:
The Database Schema and Object-Relational Mapping (ORM) layer serves as the persistent storage foundation for the bl1nk Skill IDE. It utilizes a structured data model to manage AI skills, their versions, credentials, and application settings. The system is designed to provide end-to-end type safety across the monorepo by leveraging Prisma for backend operations and TypeScript schemas for client-side data handling.
In the current development phase (Phase 1), the project utilizes SQLite as the primary database engine. The architecture ensures that data flows seamlessly between the tRPC-based backend and the Next.js/Electron frontend, maintaining state across the desktop application.
Sources: Comprehensive.md, specs/main/plan.md
Core Data Architecture
The data architecture is centered around the concept of a "Skill" and its lifecycle management. The schema supports complex relationships between skills, their iterative versions, and the secure storage of API credentials required for AI integrations.
Entity Relationship Overview
The primary entities in the system follow a hierarchical and relational structure as illustrated below:
The diagram represents the 1:N relationship between Skills and their subsequent versions, messages, and credentials.
Sources: Comprehensive.md:37-41, specs/main/plan.md
Database Schema Definitions
The project maintains detailed schema definitions for its core models. These models manage the metadata and configurations for AI skills, ensuring that every version and test interaction is recorded.
Primary Data Models
| Model | Fields | Type | Description |
|---|---|---|---|
| Skill | id, name, description | String | The root entity representing an AI skill. |
| SkillVersion | id, skillId, config | String/JSON | Represents a specific iteration or version of a skill. |
| ApiCredential | id, provider, token | String | Encrypted credentials for AWS Bedrock or OpenRouter. |
| TestMessage | id, content, timestamp | String/Date | Ephemeral messages generated during skill testing. |
| AppSettings | key, value | String | Global configuration key-value pairs for the IDE. |
Sources: Comprehensive.md:37-41, apps/server/prisma/schema.prisma, apps/client/src/db/schema.ts
TypeScript Integration
For client-side type safety and local data management, the project mirrors these models in TypeScript. This ensures that the frontend can interact with data using the same structures defined in the backend.
interface User {
id: string;
email: string;
role: 'admin' | 'editor' | 'viewer';
createdAt: Date;
}
interface UserModel {
id: string;
email: string;
role: UserRole;
}
Sources: plugins/spec-kit-skill/skills/spec-kit-skill/SKILL.md:368-373, plugins/kiro-skill/skills/kiro-skill/SKILL.md:154-158
ORM & Database Client Implementation
The project utilizes Prisma as the Object-Relational Mapper to interface with the SQLite database. This provides a type-safe API for CRUD operations, versioning, and credential management.
Implementation Components
- Prisma Client: Initialized in the server package to provide a unified data access layer.
- Prisma Validation: Integrated into the CI/CD pipeline to ensure schema integrity before deployment.
- Migrations: Managed through Prisma to track changes in the database structure over time.
Automated Database Checks
The CI/CD infrastructure includes specific workflows for database health:
prisma validate: Checks the consistency of theschema.prismafile.prisma generate: Regenerates the Prisma Client based on schema updates.
Sources: Comprehensive.md:83, apps/server/src/db/client.ts, specs/main/plan.md
Security and Encryption Strategy
A critical component of the database architecture is the secure handling of sensitive data. Credentials stored in the ApiCredential model are encrypted before being persisted to the SQLite database.
- Credential Router: Manages the CRUD operations for AI provider tokens.
- Encryption Logic: Implemented within the service layer to ensure that tokens are never stored in plain text.
- Environment Validation: The system verifies that necessary encryption keys and provider-specific variables (like
AWS_ACCESS_KEY_ID) are configured.
Sources: Comprehensive.md:50-51, Comprehensive.md:143-145, specs/main/plan.md
Knowledge Base & Wiki Integration
In addition to the application database, the project supports a "Knowledge Capture" system for Notion. This involves mapping conversation data to specific documentation schemas within Notion databases.
Documentation Database Schema
| Property | Type | Purpose |
|---|---|---|
| Title | title | Document name |
| Type | select | Categorizes content (How-To, Concept, Decision) |
| Status | select | Tracks document lifecycle (Draft, Final) |
| Category | select | Organizes by department (Engineering, Product) |
Sources: skill/notion-knowledge-capture/notion-knowledge-capture/reference/documentation-database.md
Summary
The Database Schema & ORM system for bl1nk Skill IDE provides a robust, type-safe infrastructure for managing AI skill development. By utilizing SQLite for local storage and Prisma for relational management, it enables complex version control, secure credential handling, and seamless data flow across the monorepo. The architecture's commitment to validation through CI/CD ensures that the data model remains consistent as the project scales from Phase 1 into enterprise-grade deployment.