photos/db/schema.ts

import { pgTable, serial, integer, text, boolean, timestamp, uniqueIndex } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
import { users } from 'officerdb/auth/schema';

// The Immich accounts behind /photos, for the officer-photos sidecar.
//
// This used to be IMMICH_URL + IMMICH_API_KEY in the platform-wide `.env`, which was wrong twice over: Bun
// auto-loads `.env` into EVERY process started in the platform directory, so `officer` itself held an Immich
// credential it has no code to use — and connecting a photo library was a shell task on the server rather
// than something the owner could do from the app.
//
// It is a REGISTRY, not a single row: the owner adds any number of accounts and switches between them, the
// same shape offscale_servers uses. Two accounts on the same instance is the normal case (one key per
// Immich user), which is why the uniqueness below is on the label and not on the URL.
//
// `api_key` is encrypted at rest via ../crypto.ts. An Immich key can read and delete the entire library, so a
// DB dump must not hand it over. Encryption is confined to queries/photos.ts; nothing outside that file sees
// ciphertext, and no route ever returns the key at all.
export const photosConfig = pgTable(
  'photos_config',
  {
    id: serial('id').primaryKey(),
    userId: integer('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    /** What the owner calls this account. The switcher shows nothing else, so it has to be theirs to set. */
    label: text('label').notNull(),
    // Normalized without a trailing slash before write, so `${url}/api/...` never doubles the separator.
    url: text('url').notNull(),
    apiKey: text('api_key').notNull(), // encrypted
    /** Immich version seen at the last successful probe — shown in the UI, never used for behaviour. */
    version: text('version'),
    isActive: boolean('is_active').notNull().default(false),
    lastSeenAt: timestamp('last_seen_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Labels are how the owner tells two accounts apart — duplicates would make the switcher useless. Not
    // unique on url: several keys against one instance is the whole point.
    uniqueIndex('uq_photos_config_user_label').on(t.userId, t.label),
    // At most one active account per owner, enforced by the DB rather than by convention: a partial unique
    // index over the active rows only. setActivePhotosAccount still clears the others in a transaction, but a
    // bug there fails loudly here instead of silently leaving two accounts active and the UI picking one.
    uniqueIndex('uq_photos_config_one_active')
      .on(t.userId)
      .where(sql`${t.isActive}`),
  ],
);

// ── Which Officer account owns which Immich account ──
//
// The same shape as `jellyfin_accounts`, and for the same reason: Officer's users and Immich's are two
// populations that overlap, and this records where they meet. Without it a member has a permission grant,
// a dock tile, and no credential — which reads as a permissions fault and is not one.
//
// ── Matching is on EMAIL, not a username ──
//
// This is the one real difference from the Jellyfin side, and it is a simplification. Immich identifies an
// account by email and enforces uniqueness on it, while Jellyfin has usernames and a case-folded
// `NormalizedUsername` index to match against. Officer's `users.email` is therefore the same key on both
// sides, so pairing is an exact lookup rather than a convention. `normalizedEmail` is lowercased, because
// Immich lowercases what it stores (`ZodEmail` with a transform) and a mixed-case address would otherwise
// fail to match the account it just created.
//
// ── `adopted` earns its column here too ──
//
// False means Officer created the account and may remove it. True means it pre-existed and was merely
// linked — on this server `conceiba@gmail.com` and `fedrasantamaria@gmail.com` both had Immich accounts,
// with photographs in them, before Officer knew who they were.
export const photosAccounts = pgTable(
  'photos_accounts',
  {
    id: serial('id').primaryKey(),
    userId: integer('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    /** Which registry row this account lives on. Immich users are per-instance. */
    configId: integer('config_id')
      .notNull()
      .references(() => photosConfig.id, { onDelete: 'cascade' }),
    /** Immich's own user id — a UUID. The only handle its admin API accepts. */
    immichUserId: text('immich_user_id').notNull(),
    /** As Immich holds it, for display. */
    immichEmail: text('immich_email').notNull(),
    /** Lowercased, mirroring what Immich stores. What matching compares. */
    normalizedEmail: text('normalized_email').notNull(),
    /** False = Officer created it and may remove it. True = it pre-existed and was linked. */
    adopted: boolean('adopted').notNull().default(false),
    /**
     * That person's OWN Immich API key, encrypted — the credential their Officer session reads the library
     * with.
     *
     * Nullable, and null is a real state rather than an error: minting requires a session, a session
     * requires a password, and a password is not always ours to set. Null resolves to the 503 the UI
     * already knows how to explain, never to the owner's key.
     *
     * An Immich key survives a later password change — `UserAdminService.update` hashes and writes the
     * password and touches nothing else, verified against the running 3.1.0 image. That is what makes it
     * safe to hand somebody a temporary password for their own mobile app without this going stale.
     */
    apiKey: text('api_key'), // encrypted
    linkedAt: timestamp('linked_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // One Immich account per Officer user per instance. uniqueIndex, not unique().on() — drizzle-kit
    // re-creates named composite CONSTRAINTS on every push. See src/databases/CLAUDE.md.
    uniqueIndex('uq_photos_accounts_user_config').on(t.userId, t.configId),
    // And one Officer user per Immich account: two people cannot share a library.
    uniqueIndex('uq_photos_accounts_config_immich_user').on(t.configId, t.immichUserId),
  ],
);