PilotVault/API Server/pocketbase/pb_migrations/1720300800_add_documents.js

/// <reference path="../pb_data/types.d.ts" />

// Creates the `documents` collection: the compliance + operational document
// store for PilotVault (pilot certificates, aircraft registrations, insurance,
// airspace authorisations, contracts, …). Metadata lives here; the blob lives in
// PocketBase's own file storage *for now* — the file field is the temporary
// stand-in for the S3-compatible object store this will grow into. When that
// lands, only the blob backend changes: this metadata shape (and its
// `replaces` version chain + `expiry_date`-driven alerting) stays.
//
// Like `organizations`, user management, and the logbook, the collection is
// reached only through the API Server's superuser service account, so its API
// rules stay locked (superusers only); the API Server enforces per-role scoping
// in Go.
//
// Apply by copying into your PocketBase deployment's `pb_migrations/` directory
// and restarting. Written for PocketBase v0.22+/v0.23. Idempotent: the
// collection is created only if absent, so re-running is a no-op.
//
// Depends on 1720300200_add_organizations.js (organizations), the `users` auth
// collection, and 1720300700_add_logbook.js (drones — a document may be owned by
// a specific airframe).
migrate(
  (app) => {
    // Idempotency guard.
    try {
      app.findCollectionByNameOrId('documents')
      return // already present
    } catch (_) {
      // create below
    }

    const orgs = app.findCollectionByNameOrId('organizations')
    const users = app.findCollectionByNameOrId('users')
    const drones = app.findCollectionByNameOrId('drones')

    const documents = new Collection({
      type: 'base',
      name: 'documents',
      fields: [
        { name: 'title', type: 'text', required: true, max: 200, presentable: true },

        // What kind of paperwork this is — drives filtering + which owner makes
        // sense. Kept broad to cover pilot / aircraft / operational / business.
        {
          name: 'doc_type',
          type: 'select',
          maxSelect: 1,
          values: [
            'certificate', 'medical', 'insurance', 'background_check',
            'registration', 'maintenance', 'conformity', 'firmware', 'incident',
            'flight_log', 'checklist', 'airspace_auth', 'mission_plan',
            'risk_assessment', 'contract', 'client_insurance', 'delivery_report',
            'other',
          ],
        },

        // -- ownership: who/what the document is about --
        { name: 'owner_type', type: 'select', maxSelect: 1, values: ['pilot', 'aircraft', 'organization', 'client', 'other'] },
        {
          name: 'owner_pilot',
          type: 'relation',
          required: false,
          collectionId: users.id,
          cascadeDelete: false,
          minSelect: 0,
          maxSelect: 1,
          presentable: false,
        },
        {
          name: 'owner_drone',
          type: 'relation',
          required: false,
          collectionId: drones.id,
          cascadeDelete: false,
          minSelect: 0,
          maxSelect: 1,
          presentable: false,
        },
        // Free-form owner reference for client/other (client name, aircraft
        // serial, site…), used when no relation fits.
        { name: 'owner_ref', type: 'text', max: 200 },

        // -- identity + compliance drivers --
        // Certificate / registration / policy number.
        { name: 'reference', type: 'text', max: 200 },
        { name: 'jurisdiction', type: 'text', max: 120 },
        { name: 'issue_date', type: 'date' },
        // The single highest-value field: drives expiry alerting (expired certs =
        // grounded fleet). Blank = the document never expires.
        { name: 'expiry_date', type: 'date' },

        // Lifecycle. `archived` marks a row superseded by a newer version (see
        // `replaces`) — the chain is kept, nothing is overwritten in place.
        { name: 'status', type: 'select', maxSelect: 1, values: ['active', 'pending_review', 'archived'] },
        // Who may view — informational for now; scope is enforced by org/role.
        { name: 'access_tier', type: 'select', maxSelect: 1, values: ['pilot', 'ops', 'admin', 'client'] },

        // The blob itself (temporary PocketBase-hosted stand-in for object
        // storage). Single file, ~50 MB cap.
        { name: 'file', type: 'file', maxSelect: 1, maxSize: 52428800 },

        // Type-specific fields that shouldn't need a schema migration each — same
        // JSONB-style escape hatch used for plugin config.
        { name: 'metadata', type: 'json', maxSize: 50000 },
        { name: 'notes', type: 'text', max: 2000 },

        // -- versioning (replaces_id chain) + audit --
        {
          name: 'replaces',
          type: 'relation',
          required: false,
          collectionId: '', // self-reference; patched to documents.id after save
          cascadeDelete: false,
          minSelect: 0,
          maxSelect: 1,
          presentable: false,
        },
        { name: 'version', type: 'number', min: 1 },
        {
          name: 'uploaded_by',
          type: 'relation',
          required: false,
          collectionId: users.id,
          cascadeDelete: false,
          minSelect: 0,
          maxSelect: 1,
          presentable: false,
        },
        {
          name: 'organization',
          type: 'relation',
          required: false,
          collectionId: orgs.id,
          cascadeDelete: false,
          minSelect: 0,
          maxSelect: 1,
          presentable: false,
        },

        { name: 'created', type: 'autodate', onCreate: true, onUpdate: false },
        { name: 'updated', type: 'autodate', onCreate: true, onUpdate: true },
      ],
      indexes: [
        'CREATE INDEX `idx_documents_org` ON `documents` (`organization`)',
        'CREATE INDEX `idx_documents_owner_pilot` ON `documents` (`owner_pilot`)',
        'CREATE INDEX `idx_documents_owner_drone` ON `documents` (`owner_drone`)',
        // Partial index: only the live rows the expiry job scans, keeping it
        // cheap as archived/superseded versions accumulate.
        "CREATE INDEX `idx_documents_expiring` ON `documents` (`expiry_date`) WHERE `status` = 'active'",
      ],
    })
    app.save(documents)

    // Point the self-referential `replaces` relation at the now-created
    // collection (its id wasn't known before the first save).
    const saved = app.findCollectionByNameOrId('documents')
    const replaces = saved.fields.find((f) => f.name === 'replaces')
    if (replaces) {
      replaces.collectionId = saved.id
      app.save(saved)
    }
  },
  (app) => {
    try {
      app.delete(app.findCollectionByNameOrId('documents'))
    } catch (_) {
      // already gone
    }
  },
)