👷 build(database): added parent id and filename columns for agent_documents

docs/development/database-schema.dbml

@@ -129,6 +129,8 @@ table agent_documents {
   user_id text [not null]
   agent_id text [not null]
   document_id varchar(255) [not null]
+  parent_id uuid
+  filename text
   template_id varchar(100)
   access_self integer [not null, default: 31]
   access_shared integer [not null, default: 0]
@@ -160,6 +162,7 @@ table agent_documents {
     deleted_at [name: 'agent_documents_deleted_at_idx']
     (agent_id, deleted_at, policy_load) [name: 'agent_documents_agent_autoload_deleted_idx']
     document_id [name: 'agent_documents_document_id_idx']
+    parent_id [name: 'agent_documents_parent_id_idx']
     (agent_id, document_id, user_id) [name: 'agent_documents_agent_document_user_unique', unique]
   }
 }

packages/database/migrations/0100_add_parent_id_and_filename_for_agent_documents.sql

@@ -0,0 +1,5 @@
+ALTER TABLE "agent_documents" ADD COLUMN IF NOT EXISTS "parent_id" uuid;--> statement-breakpoint
+ALTER TABLE "agent_documents" ADD COLUMN IF NOT EXISTS "filename" text;--> statement-breakpoint
+ALTER TABLE "agent_documents" DROP CONSTRAINT IF EXISTS "agent_documents_parent_id_agent_documents_id_fk";--> statement-breakpoint
+ALTER TABLE "agent_documents" ADD CONSTRAINT "agent_documents_parent_id_agent_documents_id_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."agent_documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+CREATE INDEX IF NOT EXISTS "agent_documents_parent_id_idx" ON "agent_documents" USING btree ("parent_id");

packages/database/migrations/meta/_journal.json

@@ -700,6 +700,13 @@
       "when": 1776674965365,
       "tag": "0099_topic_status_tasks_automation_mode",
       "breakpoints": true
+    },
+    {
+      "idx": 100,
+      "version": "7",
+      "when": 1777287925574,
+      "tag": "0100_add_parent_id_and_filename_for_agent_documents",
+      "breakpoints": true
     }
   ],
   "version": "6"

packages/database/src/schemas/agentDocuments.ts

@@ -1,3 +1,4 @@
+import type { AnyPgColumn } from 'drizzle-orm/pg-core';
 import {
   index,
   integer,
@@ -61,6 +62,22 @@ export const agentDocuments = pgTable(
     documentId: varchar('document_id', { length: 255 })
       .notNull()
       .references(() => documents.id, { onDelete: 'cascade' }),
+    /**
+     * Parent VFS entry inside the same agent document tree.
+     *
+     * Null means the entry is at the ordinary VFS root. This references
+     * `agent_documents.id`, not `documents.id`.
+     */
+    parentId: uuid('parent_id').references((): AnyPgColumn => agentDocuments.id, {
+      onDelete: 'cascade',
+    }),
+    /**
+     * Ordinary VFS segment name owned by this agent document entry.
+     *
+     * This starts nullable for the schema-expansion PR and becomes non-null in the
+     * backfill/constraint PR after existing rows are migrated.
+     */
+    filename: text('filename'),
 
     /**
      * Template source label (e.g. 'claw', 'custom').
@@ -171,6 +188,7 @@ export const agentDocuments = pgTable(
       table.policyLoad,
     ),
     index('agent_documents_document_id_idx').on(table.documentId),
+    index('agent_documents_parent_id_idx').on(table.parentId),
     uniqueIndex('agent_documents_agent_document_user_unique').on(
       table.agentId,
       table.documentId,