Database schema

-- The entries: one row each, the whole entry as JSON.
CREATE TABLE rebofiles_entries (
  id     text PRIMARY KEY,
  parent text,                 -- the folder it is in; null at the top
  entry  jsonb NOT NULL        -- the entry itself: see "An entry, as it is kept"
);
CREATE INDEX rebofiles_entries_parent ON rebofiles_entries (parent);

-- Settings by name: the drive's, each person's, the passwords of links.
CREATE TABLE rebofiles_entries_settings (
  name  text PRIMARY KEY,
  value jsonb NOT NULL
);

-- One row, one number, one more at every change by any process.
CREATE TABLE rebofiles_entries_revision (
  id int PRIMARY KEY CHECK (id = 1),
  n  bigint NOT NULL
);
INSERT INTO rebofiles_entries_revision (id, n) VALUES (1, 0) ON CONFLICT (id) DO NOTHING;

-- What was done: a line each, found by the entry and the folders it is about.
CREATE TABLE rebofiles_entries_activity (
  seq    bigserial PRIMARY KEY,
  entry  text NOT NULL,        -- what it was done to
  parent text,                 -- the folder it is in (for one moved, the one it went to)
  origin text,                 -- for one moved, the folder it left
  line   jsonb NOT NULL
);
CREATE INDEX rebofiles_entries_activity_entry  ON rebofiles_entries_activity (entry, seq);
CREATE INDEX rebofiles_entries_activity_parent ON rebofiles_entries_activity (parent, seq);
CREATE INDEX rebofiles_entries_activity_origin ON rebofiles_entries_activity (origin, seq);

-- What is written in each file, to find it by: the version it is of, and its words.
CREATE TABLE rebofiles_entries_text (
  id      text PRIMARY KEY,    -- the file
  version text NOT NULL,       -- the version the text is of; another one's is not found
  body    text NOT NULL,       -- the first 100,000 characters
  words   tsvector NOT NULL    -- to_tsvector('simple', …) of the words, without case or accents
);
CREATE INDEX rebofiles_entries_text_words ON rebofiles_entries_text USING gin (words);

{
  "kind": "file",
  "id": "5b0c…",
  "name": "contract.docx",
  "parent": "a1f3…",                      // the folder; null at the top
  "owner": "ana",                          // the id identify() gave
  "created": "2026-10-20T09:12:00.000Z",
  "modified": "2026-10-26T16:40:00.000Z",
  "modifiedBy": "bea",
  "type": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
  "versions": [                            // the newest first; never empty
    {
      "id": "v2…", "key": "5b0c…/v2…",     // where the bytes are in the bucket
      "size": 48213, "at": "2026-10-26T16:40:00.000Z", "by": "bea",
      "sha256": "9f86d0…",                 // to tell the same file uploaded again
      "placeholder": "data:image/jpeg;base64,/9j/4AAQ…",
      "renditions": [                      // its first page, or a picture, made smaller
        { "size": 480, "width": 480, "height": 679, "key": "5b0c…/v2…@480", "type": "image/jpeg", "bytes": 31877 }
      ]
    },
    { "id": "v1…", "key": "5b0c…/v1…", "size": 47990, "at": "2026-10-20T09:12:00.000Z", "by": "ana" }
  ],
  "shares": [{ "with": "bea", "role": "editor", "by": "ana", "at": "2026-10-21T10:00:00.000Z" }],
  "links": [
    { "id": "l9…", "role": "viewer", "expires": "2026-11-02T00:00:00.000Z", "locked": true, "uses": 1, "used": 0, "by": "ana", "at": "2026-10-26T17:00:00.000Z" }
  ]
}

{
  "kind": "folder", "id": "a1f3…", "name": "Clients", "parent": null, "color": "teal",
  "owner": "ana", "created": "…", "modified": "…", "modifiedBy": "ana",
  "trashed": { "at": "2026-10-26T18:00:00.000Z", "by": "ana" }   // only on what was put in the trash itself
}

{
  "at": "2026-10-26T16:41:00.000Z", "by": "bea",
  "action": "renamed",                  // created, uploaded, version, renamed, moved, trashed, restored,
                                        // deleted, shared, unshared, linked, unlinked, colored
  "entry": "5b0c…", "name": "contract-final.docx", "kind": "file",
  "parent": "a1f3…",
  "was": "contract.docx"                // for one renamed; "from" for one moved; "with", "role" for one shared
}

// What `prisma db pull` reads from the tables ReboFiles made, named as an application names its models.
// `prisma migrate diff` between it and the database: an empty migration.

/// A folder or a file of the drive: the whole entry, as @rebofiles/core has it, in `entry`.
model DriveEntry {
  id     String  @id
  parent String?
  entry  Json

  @@index([parent], map: "rebofiles_entries_parent")
  @@map("rebofiles_entries")
}

/// The drive's settings, each person's, the passwords of links: by name.
model DriveSetting {
  name  String @id
  value Json

  @@map("rebofiles_entries_settings")
}

/// One row, one number: one more at every change. The table has CHECK (id = 1), which Prisma does not model.
model DriveRevision {
  id Int    @id
  n  BigInt

  @@map("rebofiles_entries_revision")
}

/// What was done, a line each.
model DriveActivity {
  seq    BigInt  @id @default(autoincrement())
  entry  String
  parent String?
  origin String?
  line   Json

  @@index([entry, seq], map: "rebofiles_entries_activity_entry")
  @@index([parent, seq], map: "rebofiles_entries_activity_parent")
  @@index([origin, seq], map: "rebofiles_entries_activity_origin")
  @@map("rebofiles_entries_activity")
}

/// What is written in each file, to find it by.
model DriveText {
  id      String                  @id
  version String
  body    String
  words   Unsupported("tsvector")

  @@index([words], map: "rebofiles_entries_text_words", type: Gin)
  @@map("rebofiles_entries_text")
}

// lib/db.ts: one pool, for your Prisma and for the drive.
import { PrismaPg } from '@prisma/adapter-pg';
import pg from 'pg';
import { PrismaClient } from '@/lib/generated/prisma/client';

export const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
export const prisma = new PrismaClient({ adapter: new PrismaPg(pool) });

// app/api/files/[...path]/route.ts
import { byRoles, createDriveHandler, postgresMetadata, s3OptionsFrom, s3Storage } from '@rebofiles/server';
import { pool } from '@/lib/db';

const handle = createDriveHandler({
  storage: s3Storage(s3OptionsFrom(process.env)),
  metadata: await postgresMetadata(pool), // a pool: each change holds a lock of the database
  identify, authorize: byRoles, secret: process.env.REBOFILES_SECRET!, base: '/api/files',
});

# The tables are there already, made by ReboFiles: add the models, then tell Prisma they exist.
npx prisma db pull                      # or paste the models above into schema.prisma
mkdir -p prisma/migrations/0_rebofiles
npx prisma migrate diff --from-empty --to-schema prisma/schema.prisma --script > prisma/migrations/0_rebofiles/migration.sql
npx prisma migrate resolve --applied 0_rebofiles

# From then on, migrate dev leaves them as they are: its next migration about them is empty.
npx prisma migrate dev

# A new database: your migrations make the tables first, ReboFiles finds them when it starts.
npx prisma migrate deploy

import { Prisma } from '@/lib/generated/prisma/client';
import type { Entry } from '@rebofiles/core';
import { prisma } from '@/lib/db';

// Ana's files and folders.
const anas = await prisma.driveEntry.findMany({ where: { entry: { path: ['owner'], equals: 'ana' } } });
const names = anas.map((row) => (row.entry as unknown as Entry).name);

// What is in a folder; the folders at the top.
const inside = await prisma.driveEntry.findMany({ where: { parent: folderId } });
const top = await prisma.driveEntry.findMany({ where: { parent: null, entry: { path: ['kind'], equals: 'folder' } } });

// What was done to it, and in it.
const lines = await prisma.driveActivity.findMany({
  where: { OR: [{ entry: folderId }, { parent: folderId }] },
  orderBy: { seq: 'desc' },
  take: 20,
});

// A person's own settings.
const mine = await prisma.driveSetting.findUnique({ where: { name: 'person:ana' } });

// The bytes each person keeps: what JSON paths cannot say, in SQL.
const bytes = await prisma.$queryRaw<{ owner: string; bytes: number }[]>(Prisma.sql`
  SELECT entry->>'owner' AS owner, sum((v->>'size')::bigint)::float8 AS bytes
  FROM rebofiles_entries, jsonb_array_elements(entry->'versions') v
  GROUP BY 1`);

// Files by what is written in them: words is Unsupported("tsvector"), so in SQL too.
const found = await prisma.$queryRaw<{ id: string; body: string }[]>(Prisma.sql`
  SELECT id, body FROM rebofiles_entries_text WHERE words @@ to_tsquery('simple', ${'payment & 30'})`);

-- Each person's files, and the bytes they keep (every version counts, as the quota counts them).
SELECT entry->>'owner' AS owner,
       count(*) AS files,
       sum((SELECT sum((v->>'size')::bigint) FROM jsonb_array_elements(entry->'versions') v)) AS bytes
FROM rebofiles_entries
WHERE entry->>'kind' = 'file'
GROUP BY 1 ORDER BY bytes DESC;

-- What is shared with Bea, directly.
SELECT entry->>'name', s->>'role'
FROM rebofiles_entries, jsonb_array_elements(entry->'shares') s
WHERE s->>'with' IN ('bea', '*');

-- What was put in the trash before a date.
SELECT id, entry->>'name' FROM rebofiles_entries WHERE (entry->'trashed'->>'at') < '2026-10-01';

-- What Bea did this week.
SELECT line FROM rebofiles_entries_activity WHERE line->>'by' = 'bea' AND line->>'at' > '2026-10-20' ORDER BY seq DESC;

-- An index for a query you make often, as for the owner:
CREATE INDEX rebofiles_entries_owner ON rebofiles_entries ((entry->>'owner'));

import type { MetadataStore } from '@rebofiles/server';

export const myStore: MetadataStore = {
  async load() { /* every entry, as driveOf(entries) */ },
  async save({ put, removed }) { /* upsert put by id, delete removed: in one transaction */ },
  // Optional; without them the server keeps each in the process.
  async revision() { /* a value that changes with every save, by any process */ },
  async lock(work) { /* work() while no other process saves */ },
  async getSetting(name) {}, async setSetting(name, value) {},
  async record(lines) {}, async activity(id, limit) {},
  async keepText(id, version, text) {}, async findText(query, limit) {},
};