feat: Drizzle ORM for improved data transformation

[madjin]

Add Drizzle ORM for Improved Data Transformation

Overview

Implement Drizzle ORM to replace current manual SQL operations and data transformations in the storage layer.

Background

Currently, the codebase has several implicit data transformations:

• Source plugins convert external data to ContentItem objects
• Generator components transform content into structured summaries
• We manually map between database records and application models

Drizzle ORM would provide type-safe schema definitions and simplify these transformations.

Proposed Solution

1. Create schema definitions for ContentItem and SummaryItem models
2. Implement a new DrizzleStorage class that implements the StoragePlugin interface
3. Add configuration support for the new storage implementation

Benefits

Drizzle ORM would help by:

1. Replacing manual data mapping in SQLiteStorage with type-safe schema definitions
2. Automating JSON serialization/deserialization for complex fields
3. Eliminating redundant transformation code between database and application models
4. Providing consistent data validation and conversion across the application

This would reduce code complexity and increase type safety in the data transformation pipeline.

Implementation Complexity

• Medium effort (estimated 1-2 days of development time)
• Can be implemented incrementally alongside existing storage
  • Would be a new source that can be loaded from config

Next Steps

• Investigate Drizzle ORM compatibility with SQLite
  • For inspiration, see https://github.com/elizaOS/elizaos.github.io

[madjin]

Comparing https://github.com/elizaOS/elizaos.github.io (typescript + drizzle + sqlite, what we use for GitHub data) with AI-news for ideas / inspiration:

---

I've analyzed the code from the Eliza Leaderboard project, and there are several opportunities for integration or inspiration with your AI News Aggregator:

Main Similarities & Reusable Components

1. Drizzle ORM Integration: The Eliza project has a comprehensive Drizzle ORM implementation that could solve your data transformation needs:

   • Well-defined schema with relations in src/lib/data/schema.ts
   • Separation of database connections for different environments (db.ts and db-nextjs.ts)
   • Query helpers in src/lib/pipelines/queryHelpers.ts for building common conditions

2. Pipeline Architecture: Their modular pipeline system is more advanced than yours:

   • Functional programming approach with composable operations
   • Type-safe pipeline steps via createStep, pipe(), parallel(), and mapStep()
   • Context objects that flow through pipeline steps

3. Date Handling: Extensive date utilities in src/lib/date-utils.ts:

   • Interval generation (daily, weekly, monthly)
   • Date range calculations
   • Formatting helpers

4. Ingest System: Their GitHub data ingestion pipeline has similarities to your content sources:

   • Fetches data from external APIs
   • Transforms into standardized format
   • Stores in database with proper relations

Integration Opportunities

1. Replace Your Storage Layer:

   • Adopt their Drizzle schema approach for your ContentItem and SummaryItem types
   • Use their query helpers for standardized database operations
   • Leverage their token-based rate limiting for API calls

2. Upgrade Your Pipeline System:

   • Replace your aggregator classes with their functional pipeline pattern
   • Adopt their context-based approach for passing configuration
   • Use their retry mechanisms for API calls (retryOperation helper)

3. Summary Generation:

   • Their AI summarization utilities in src/lib/pipelines/summarize are more advanced
   • Could replace your prompt helpers with their structured approach

Implementation Approach

Here's how I'd recommend approaching this:

1. Start with Drizzle Integration:

   • Copy their schema.ts as a reference
   • Create your own schema based on your ContentItem and SummaryItem models
   • Implement their migration approach for your database

2. Adapt Pipeline System:

   • Start with their pipeline types and utilities
   • Convert your existing sources to their pipeline pattern
   • Gradually migrate each component

3. Keep Best Parts of Both:

   • Your source plugins have more variety (Twitter, Discord, etc.)
   • Their GitHub integration is more robust
   • Combine the strengths of both approaches

Code Example: Your Schema in Drizzle

Here's how you might start implementing a Drizzle schema for your existing types:

// src/lib/data/schema.ts
import { sql, relations } from "drizzle-orm";
import { sqliteTable, text, integer, index } from "drizzle-orm/sqlite-core";

// Content items table
export const contentItems = sqliteTable(
  "content_items",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    cid: text("cid").notNull(),
    type: text("type").notNull(),
    source: text("source").notNull(),
    title: text("title"),
    text: text("text"),
    link: text("link"),
    topics: text("topics"), // JSON string of topics array
    date: integer("date"), // Unix timestamp
    metadata: text("metadata").default("{}"), // JSON string
    lastUpdated: text("last_updated")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("idx_content_items_cid").on(table.cid),
    index("idx_content_items_type").on(table.type),
    index("idx_content_items_source").on(table.source),
    index("idx_content_items_date").on(table.date),
  ]
);

// Summary items table
export const summaryItems = sqliteTable(
  "summary_items",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    type: text("type").notNull(),
    title: text("title"),
    categories: text("categories"), // JSON string
    markdown: text("markdown"),
    date: integer("date"), // Unix timestamp
    lastUpdated: text("last_updated")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => [
    index("idx_summary_items_type").on(table.type),
    index("idx_summary_items_date").on(table.date),
  ]
);

// Define relations
export const contentItemsRelations = relations(contentItems, ({ many }) => ({
  // You could add relations if needed
}));

This is a starting point that would allow you to migrate gradually to the Drizzle ORM approach while maintaining compatibility with your existing data structure.