# How to Manage Fastio File Metadata with Drizzle ORM

Managing Fastio file metadata with Drizzle ORM lets developers store and query agent workspace data in Postgres with full type safety. This approach syncs file details like IDs, sizes, and AI states from the Fastio API to a local database. Caching metadata reduces repeated API calls and enables complex relational queries beyond API limits.


Source: https://fast.io/resources/manage-fastio-file-metadata-drizzle-orm/
Last reviewed: 2026-02-24

## Why Cache Fastio File Metadata Locally?

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.

## 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`:

```typescript
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 Fastio Files

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

```typescript
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.

## Fetch Metadata from Fastio API

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

Example fetch workspace files:

```typescript
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.

```typescript
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 a schedule or trigger from the Fastio WebSocket events feed or activity polling.

## Query Cached Metadata with Drizzle

Leverage type-safe queries for analytics.

Recent ready files:

```typescript
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.

## Frequently asked questions

### How do I store Fastio file IDs in a database?

Use the opaque `node_id` as primary key text column. Upsert on ID to handle updates. Include `workspace_id` for multi-workspace support.

### Can I use Drizzle ORM with external APIs like Fastio?

Yes, fetch API data then insert/upsert via Drizzle. Schema mirrors API response for type safety. Run syncs on schedules or via the WebSocket events feed.

### What Fastio metadata fields to cache?

Core: node_id, name, size_bytes, mime_type, created_at, updated_at, ai_state, version_id, parent_id. Optional: preview_url, path.

### How to handle Fastio pagination in sync?

Use cursor-based pagination with `next_cursor`. Loop until no cursor, collecting files across pages.

### Does caching affect Fastio credits?

Local queries use no credits. Sync fetches consume bandwidth credits based on list response size.

## About Fast.io

Fast.io provides shared workspaces where people and AI agents work on the same files, with built-in semantic search and citation-backed chat over what they hold. Agents reach it through a remote MCP server at https://mcp.fast.io/mcp, a REST API at https://api.fast.io/current/, and a command line client published on npm as @vividengine/fastio-cli.