Database Schema & ORM

Relevant source files

The following files were used as context for generating this wiki page:

• packages/api/src/db/schema/wiki.ts
• packages/api/drizzle/0035_wiki.sql
• packages/api/src/repositories/wikiPage.ts
• packages/api/src/services/wikiService.ts
• packages/api/src/test/productionMigrationChain.test.ts
• packages/api/src/test/schemaValidation.test.ts
• packages/shared/src/types/wiki.ts

Orcy utilizes a structured relational database schema managed via Drizzle ORM and backed by SQLite (specifically better-sqlite3 in production). The schema is designed to support a hierarchical task orchestration model involving Habitats, Missions, and Tasks, while incorporating advanced features like an authored Habitat Wiki, version history, and polymorphic citations.

The persistence layer follows a repository pattern where Drizzle schema definitions serve as the single source of truth for table, column, and index declarations. This ensures type safety across the monorepo, from the API server to shared types used by MCP tools. The system utilizes automated migrations to maintain schema parity across different deployment postures, ranging from local development to remote production environments.

Sources: CONTRIBUTING.md:158-161, packages/api/src/test/productionMigrationChain.test.ts:1-12

Core Data Model & Hierarchies

The primary organizational structure in Orcy is a three-tier hierarchy: Habitats act as top-level containers, Missions represent high-level product initiatives or kanban cards, and Tasks are specific units of work claimed by agents.

Habitat Wiki Architecture

The Wiki system is a prominent module within the schema, providing a versioned knowledge layer. It implements a tree hierarchy through self-referential links and maintains append-only version history.

Table	Purpose	Relationships
wiki_pages	Current state and denormalized content for wiki entries.	References habitats and self-references via parent_id.
wiki_page_versions	Append-only history of every save and restore operation.	References wiki_pages.
wiki_page_links	Polymorphic citations linking pages to other entities (Tasks, Missions, Pulses).	References wiki_pages.
wiki_coverage_markers	Watermarks that track which time windows have been covered by documentation.	References habitats and wiki_pages.

Sources: packages/api/src/db/schema/wiki.ts:7-92, packages/shared/src/types/wiki.ts:20-72

Schema Implementation with Drizzle

Orcy defines its schema using Drizzle's SQLite core primitives. Key implementation details include the use of text for UUIDs and ISO-8601 timestamps, and integer for boolean flags and version numbers.

Wiki Pages Definition

The wiki_pages table utilizes partial unique indexes to manage slugs. Root pages (where parent_id is NULL) must have unique slugs per habitat, while child pages must have unique slugs relative to their parent.

export const wikiPages = sqliteTable(
  "wiki_pages",
  {
    id: text("id").primaryKey(),
    habitatId: text("habitat_id")
      .notNull()
      .references(() => habitats.id, { onDelete: "cascade" }),
    parentId: text("parent_id").references((): AnySQLiteColumn => wikiPages.id, {
      onDelete: "no action",
    }),
    slug: text("slug").notNull(),
    title: text("title").notNull(),
    content: text("content").notNull().default(""),
    status: text("status", { enum: ["draft", "published"] })
      .notNull()
      .default("draft"),
    tags: text("tags", { mode: "json" }).$type<string[]>().notNull(),
    // ... metadata fields
  },
  (table) => [
    uniqueIndex("idx_wiki_pages_slug_root")
      .on(table.habitatId, table.slug)
      .where(isNull(table.parentId)),
    uniqueIndex("idx_wiki_pages_slug_child")
      .on(table.habitatId, table.parentId, table.slug)
      .where(sql`parent_id IS NOT NULL`),
  ]
);

Sources: packages/api/src/db/schema/wiki.ts:7-46, packages/api/drizzle/0035_wiki.sql:1-24

Full-Text Search (FTS5) Integration

For search capabilities within the Habitat Wiki, the schema implements a virtual table using the SQLite FTS5 extension. This allows for high-performance free-text queries over titles and content.

1. Virtual Table: wiki_pages_fts is created using content='wiki_pages' to index the main table.
2. Triggers: Automated triggers (wiki_pages_ai, wiki_pages_ad, wiki_pages_au) keep the FTS index synchronized after every insert, delete, or update operation on the base wiki_pages table.
3. Search Logic: The repository layer implements a search function that uses the MATCH operator and bm25() ranking when FTS5 is available, falling back to standard LIKE queries in environments (like certain test runners) where FTS5 is missing.

Sources: packages/api/drizzle/0035_wiki.sql:72-87, packages/api/src/repositories/wikiPage.ts:167-219

Migration and Validation Framework

The project enforces a strict migration chain to ensure data integrity during upgrades. The baseline schema (0000_schema.sql) contains 64 tables and 171 indexes, covering everything from core orchestration to plugins and audit trails.

Safety Gates

• Production Migration Test: A focused suite (productionMigrationChain.test.ts) verifies that fresh installs, upgrades from legacy versions (e.g., v0.29), and reconciliation of prerelease markers (like the 0053 quarantine reset) function without data loss.
• Schema Parity: The system verifies that representative repository operations (read/write) function correctly across all domains including Workflows, Wiki, Triage, and Releases after migrations are applied.

Sources: packages/api/src/test/productionMigrationChain.test.ts:1-20, packages/api/src/test/schemaValidation.test.ts:25-115

Summary

The Orcy database architecture balances relational integrity with flexible documentation and orchestration features. By leveraging Drizzle ORM and SQLite's FTS5, it provides a performant, type-safe environment for coordinating AI agents within habitats while maintaining a detailed audit trail and versioned knowledge base.