How to Manage Fast.io File Metadata with Drizzle ORM

Why Cache Fast.io File Metadata Locally?

Fast.io workspaces store files for agentic teams, with metadata like file IDs, names, sizes, MIME types, timestamps, and AI processing states. Querying this data directly via the API works for simple cases. But for frequent access, analytics, or joining with other data, API rate limits and latency add up.

Local caching in Postgres cuts API dependency. Drizzle ORM provides 100% TypeScript type safety for schema and queries. Developers gain relational power, like joining file metadata with user activity or custom tags.

This setup supports agent workflows. Agents upload files, metadata syncs automatically, and queries run locally for speed.

Set Up Drizzle ORM with Postgres

Install Drizzle and Postgres driver:

npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

Configure drizzle.config.ts:

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Set DATABASE_URL in .env. Run npx drizzle-kit generate:pg and npx drizzle-kit push:pg to apply schema.

Define Drizzle Schema for Fast.io Files

Map Fast.io storage nodes to a files table. Key fields from API: node_id (opaque string), workspace_id (profile ID), name, size_bytes, mime_type, created_at, updated_at, type ('file'), ai_state ('ready', etc.), version_id.

import { pgTable, text, bigint, timestamp, varchar, pgEnum } from 'drizzle-orm/pg-core';

const aiStateEnum = pgEnum('ai_state', ['disabled', 'pending', 'in_progress', 'ready', 'failed']);

export const files = pgTable('files', {
  id: text('node_id').primaryKey(), // Fast.io opaque node ID
  workspaceId: text('workspace_id').notNull(),
  parentId: text('parent_id'), // Folder node ID
  name: varchar('name', { length: 256 }).notNull(),
  path: text('path'), // Computed full path
  sizeBytes: bigint('size_bytes', { mode: 'number' }),
  mimeType: varchar('mime_type', { length: 128 }),
  type: varchar('type', { length: 10 }).notNull().default('file'), // file/folder/note
  aiState: aiStateEnum('ai_state'),
  versionId: text('version_id'),
  createdAt: timestamp('created_at').notNull(),
  updatedAt: timestamp('updated_at').notNull(),
});

export type File = typeof files.$inferSelect;

This schema matches Fast.io responses. Generate migrations with Drizzle Kit.

Fetch Metadata from Fast.io API

Authenticate with API key (Bearer token). List files recursively or by cursor.

Example fetch workspace files:

async function fetchWorkspaceFiles(workspaceId: string, token: string, parentId = 'root') {
  let cursor = '';
  const allFiles: any[] = [];
  do {
    const url = `https://api.fast.io/current/workspace/${workspaceId}/storage/${parentId}/list/?page_size=500&sort_by=updated&sort_dir=desc${cursor ? `&cursor=${cursor}` : ''}`;
    const res = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });
    const data = await res.json();
    if (data.result) {
      allFiles.push(...data.response.filter((item: any) => item.type === 'file'));
      cursor = data.pagination.next_cursor;
    }
  } while (cursor);
  return allFiles;
}

Handle pagination with next_cursor. Filter type === 'file'.

Sync Metadata to Drizzle with Upserts

Use Drizzle transactions for atomic upserts. Map API fields to insert.

import { db } from './db';
import { files } from './schema';
import { eq } from 'drizzle-orm';

async function syncFiles(workspaceFiles: any[]) {
  await db.transaction(async (tx) => {
    for (const file of workspaceFiles) {
      await tx.insert(files)
        .values({
          id: file.node_id,
          workspaceId: workspaceId,
          parentId: file.parent_id,
          name: file.name,
          sizeBytes: BigInt(file.size_bytes),
          mimeType: file.mime_type,
          type: file.type,
          aiState: file.ai?.state,
          versionId: file.version,
          createdAt: new Date(file.created_at),
          updatedAt: new Date(file.updated_at),
        })
        .onConflictDoUpdate({
          target: files.id,
          set: {
            parentId: file.parent_id,
            name: file.name,
            // ... other fields
            updatedAt: new Date(file.updated_at),
          },
        });
    }
  });
}

Run on cron or webhook trigger from Fast.io events.

Query Cached Metadata with Drizzle

Leverage type-safe queries for analytics.

Recent ready files:

const recentReadyFiles = await db.select()
  .from(files)
  .where(and(
    eq(files.aiState, 'ready'),
    gt(files.updatedAt, new Date(Date.now() - 24*60*60*1000))
  ))
  .orderBy(desc(files.updatedAt));

Joins for folder structure or workspace stats.

This enables dashboards, agent decisions based on local data.