How to Manage Fastio File Metadata with Drizzle ORM

Fastio 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.

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.

Build agent workspaces with persistent metadata?

Get 50GB free storage and 5,000 monthly credits. No credit card needed for agents.

Start Free Agent Tier

Map Fastio 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(), // Fastio 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 Fastio responses. Generate migrations with Drizzle Kit.

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'.

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 Fastio events.

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.