Skip to content

⭐️⭐️⭐️ Open-source, self-hosted alternative to Linear and Plane. Project management and issue tracking where teams and AI agents work side by side to plan and ship products.

35.3 KB
Raw Blame
import {
  db,
  project,
  projectColumn,
  label,
  cycle,
  issue,
  issueLabel,
  issueActivity,
  issueLink,
  issueAttachment,
  issueWatcher,
  issueChecklist,
  issueChecklistItem,
  customField,
  customFieldOption,
  issueFieldValue,
  issueFieldOption,
  user,
  projectMember,
  importJob,
  importRecord,
  getStorageSettings,
  mimeAllowed,
  MB,
  projectStoredBytes,
  lockAttachmentStorage,
} from '@repo/db';
import { and, eq, gt, isNull, isNotNull, sql, type Column } from 'drizzle-orm';
import { decryptSecret, type EncryptedSecret } from '@repo/crypto';
import {
  putObject,
  deleteObject,
  safeAttachmentFilename,
  attachmentObjectKey,
} from '@repo/storage';
import type {
  CanonicalState,
  CanonicalLabel,
  CanonicalCycle,
  CanonicalCustomField,
  CanonicalChecklist,
} from './canonical';
import { AttachmentRejectedError } from './attachment-download';

export { AttachmentRejectedError };

// All @repo/db access for a source import: claiming due import_job rows (the
// same FOR UPDATE SKIP LOCKED + lease pattern as store.ts uses for
// webhook_delivery), reading/writing a job's progress, the import_record
// upsert primitive, and creating the local rows an import produces. The
// credential decrypt lives here rather than packages/db/src/domains/: that
// directory is for config more than one process reads, and only the worker
// ever decrypts an import job's stored credential — the api only sees the raw
// token once, at creation, before it is encrypted.

const LEASE_SECONDS = 120;

export interface ClaimedImportJob {
  id: number;
  projectId: number;
  source: string;
  phase: string;
  status: string;
  config: Record<string, unknown>;
  cursor: Record<string, unknown>;
  attempts: number;
  credentialCiphertext: string | null;
  credentialIv: string | null;
  credentialAuthTag: string | null;
}

// Claims up to `limit` due import jobs (default 1: import-worker.ts processes
// one job per tick). Claiming pushes next_attempt_at forward by the lease and
// bumps attempts, so a job whose worker crashes mid-tick becomes claimable
// again once the lease expires — status stays 'pending' throughout, the same
// as webhook_delivery, so the lease alone drives crash recovery.
//
// last_error is cleared here too, the moment a claim starts a new attempt —
// not only on success. The Settings UI reads a non-null last_error as "waiting
// out a retry" (see SettingsImportExportJobRow.tsx), computing the countdown
// from next_attempt_at; without this, a poll landing while the new attempt is
// still in flight would show the old error next to the lease's own deadline
// (up to LEASE_SECONDS out) as if it were the retry countdown, even though the
// job is actively working, not waiting.
export async function claimDueImportJobs(limit = 1): Promise<ClaimedImportJob[]> {
  const rows = await db.execute(sql`
    UPDATE import_job j
    SET attempts = j.attempts + 1,
        next_attempt_at = now() + make_interval(secs => ${LEASE_SECONDS}),
        last_error = NULL
    WHERE j.id IN (
      SELECT id FROM import_job
      WHERE status = 'pending' AND next_attempt_at <= now()
      ORDER BY next_attempt_at
      FOR UPDATE SKIP LOCKED
      LIMIT ${limit}
    )
    RETURNING
      j.id,
      j.project_id AS "projectId",
      j.source,
      j.phase,
      j.status,
      j.config,
      j.cursor,
      j.attempts,
      j.credential_ciphertext AS "credentialCiphertext",
      j.credential_iv AS "credentialIv",
      j.credential_auth_tag AS "credentialAuthTag"
  `);
  return rows as unknown as ClaimedImportJob[];
}

export function decryptImportCredential(job: ClaimedImportJob): unknown {
  if (!job.credentialCiphertext || !job.credentialIv || !job.credentialAuthTag) {
    throw new Error(`import job ${job.id} has no stored credential`);
  }
  const encrypted: EncryptedSecret = {
    ciphertext: job.credentialCiphertext,
    iv: job.credentialIv,
    authTag: job.credentialAuthTag,
  };
  return JSON.parse(decryptSecret(encrypted));
}

// A successful tick clears the claim lease (nextAttemptAt) and lastError, and
// resets attempts, so the next tick can claim the job right away instead of
// waiting out the lease claimDueImportJobs set, and a resolved rate limit or
// transient failure doesn't linger as if it were still happening. `attempts`
// counts consecutive claims with no successful tick in between, not ticks
// claimed overall, so a transient error only fails the job after MAX_ATTEMPTS
// in a row.
export async function saveImportJobCursor(jobId: number, cursor: object): Promise<void> {
  await db
    .update(importJob)
    .set({ cursor, attempts: 0, nextAttemptAt: sql`now()`, lastError: null, updatedAt: new Date() })
    .where(eq(importJob.id, jobId));
}

export async function advanceImportJobPhase(
  jobId: number,
  phase: string,
  cursor: object,
): Promise<void> {
  await db
    .update(importJob)
    .set({
      phase,
      cursor,
      attempts: 0,
      nextAttemptAt: sql`now()`,
      lastError: null,
      updatedAt: new Date(),
    })
    .where(eq(importJob.id, jobId));
}

// Terminal states clear the credential columns: nothing needs to read them
// again once the job can no longer make progress.
export async function completeImportJob(jobId: number): Promise<void> {
  await db
    .update(importJob)
    .set({
      status: 'completed',
      phase: 'done',
      lastError: null,
      updatedAt: new Date(),
      credentialCiphertext: null,
      credentialIv: null,
      credentialAuthTag: null,
    })
    .where(eq(importJob.id, jobId));
}

export async function failImportJob(jobId: number, error: string): Promise<void> {
  await db
    .update(importJob)
    .set({
      status: 'failed',
      lastError: error.slice(0, 500),
      updatedAt: new Date(),
      credentialCiphertext: null,
      credentialIv: null,
      credentialAuthTag: null,
    })
    .where(eq(importJob.id, jobId));
}

// A transient failure (rate limited, a network error, an unreachable
// instance): reschedule rather than fail the job outright. A tick that does not
// count as an attempt (a rate limit) gives back the bump its claim made.
export async function retryImportJobLater(
  jobId: number,
  delayMs: number,
  error: string,
  countsAsAttempt: boolean,
): Promise<void> {
  const delaySeconds = Math.max(1, Math.ceil(delayMs / 1000));
  await db
    .update(importJob)
    .set({
      ...(countsAsAttempt ? {} : { attempts: sql`greatest(${importJob.attempts} - 1, 0)` }),
      nextAttemptAt: sql`now() + make_interval(secs => ${delaySeconds})`,
      lastError: error.slice(0, 500),
      updatedAt: new Date(),
    })
    .where(eq(importJob.id, jobId));
}

export type ImportEntityType =
  'issue' | 'comment' | 'label' | 'state' | 'cycle' | 'attachment' | 'checklist' | 'custom_field';

// Bulk id-only snapshot write for the Discover phase: rows start with no local
// mapping. Idempotent — a source id already recorded (from an earlier attempt
// at this same phase) is left alone.
export async function insertDiscoveredIds(
  jobId: number,
  entityType: ImportEntityType,
  sourceIds: string[],
): Promise<void> {
  if (sourceIds.length === 0) return;
  await db
    .insert(importRecord)
    .values(
      sourceIds.map((sourceId) => ({ importJobId: jobId, sourceEntityType: entityType, sourceId })),
    )
    .onConflictDoNothing();
}

// Runs against either the plain db or a transaction, so the local-row insert
// and the import_record upsert below can share one transaction and commit
// atomically — see createLocalStateAndRecord/createLocalCycleAndRecord/
// createLocalIssueAndRecord.
type ImportDbExecutor = typeof db | Parameters<Parameters<typeof db.transaction>[0]>[0];

// The idempotent-upsert primitive keyed on the unique index
// (import_job_id, source_entity_type, source_id): inserts a fresh mapping, or
// overwrites the local mapping of one that already exists (Discover wrote it
// with no local id, Create is now filling it in).
async function upsertImportRecordWith(
  executor: ImportDbExecutor,
  jobId: number,
  entityType: ImportEntityType,
  sourceId: string,
  localEntityType: ImportEntityType | null,
  localId: number | null,
  sourceDisplayId: string | null = null,
): Promise<void> {
  await executor
    .insert(importRecord)
    .values({
      importJobId: jobId,
      sourceEntityType: entityType,
      sourceId,
      sourceDisplayId,
      localEntityType,
      localId,
    })
    .onConflictDoUpdate({
      target: [importRecord.importJobId, importRecord.sourceEntityType, importRecord.sourceId],
      set: { localEntityType, localId, sourceDisplayId },
    });
}

// True for a local row this job has not mapped yet. A row the same job already
// mapped belongs to another source record, so it is never that record's duplicate:
// two source issues may share a title, two comments a body and a timestamp. A row an
// earlier job mapped stays reusable, which is what makes a re-run not duplicate.
function notMappedByJob(jobId: number, entityType: ImportEntityType, localId: Column) {
  return sql`NOT EXISTS (
    SELECT 1 FROM ${importRecord}
    WHERE ${importRecord.importJobId} = ${jobId}
      AND ${importRecord.sourceEntityType} = ${entityType}
      AND ${importRecord.localId} = ${localId}
  )`;
}

export async function upsertImportRecord(
  jobId: number,
  entityType: ImportEntityType,
  sourceId: string,
  localEntityType: ImportEntityType | null,
  localId: number | null,
): Promise<void> {
  await upsertImportRecordWith(db, jobId, entityType, sourceId, localEntityType, localId);
}

export async function findImportRecord(
  jobId: number,
  entityType: ImportEntityType,
  sourceId: string,
): Promise<{ localId: number | null } | null> {
  const rows = await db
    .select({ localId: importRecord.localId })
    .from(importRecord)
    .where(
      and(
        eq(importRecord.importJobId, jobId),
        eq(importRecord.sourceEntityType, entityType),
        eq(importRecord.sourceId, sourceId),
      ),
    )
    .limit(1);
  return rows[0] ?? null;
}

// Resolves a cross-reference like "ROOMS-524" during the Rewrite phase: the
// captured number is the source's own display id, not its sourceId (a UUID for
// Plane), so this looks up by the column Create stashed it in instead.
export async function findImportRecordByDisplayId(
  jobId: number,
  entityType: ImportEntityType,
  sourceDisplayId: string,
): Promise<{ localId: number | null } | null> {
  const rows = await db
    .select({ localId: importRecord.localId })
    .from(importRecord)
    .where(
      and(
        eq(importRecord.importJobId, jobId),
        eq(importRecord.sourceEntityType, entityType),
        eq(importRecord.sourceDisplayId, sourceDisplayId),
      ),
    )
    .limit(1);
  return rows[0] ?? null;
}

export interface ImportRecordRow {
  id: number;
  sourceId: string;
}

export async function listImportRecords(
  jobId: number,
  entityType: ImportEntityType,
  afterId: number,
  limit: number,
): Promise<ImportRecordRow[]> {
  return db
    .select({ id: importRecord.id, sourceId: importRecord.sourceId })
    .from(importRecord)
    .where(
      and(
        eq(importRecord.importJobId, jobId),
        eq(importRecord.sourceEntityType, entityType),
        gt(importRecord.id, afterId),
      ),
    )
    .orderBy(importRecord.id)
    .limit(limit);
}

// The next chunk of this job's snapshot that has not been materialized yet,
// keyset-paginated on import_record's own id — itsaplan's own position in the
// snapshot list, not the source's pagination cursor (see the notes file). A
// retried tick naturally skips rows a crashed earlier attempt already created,
// since those no longer match "local id still null".
export async function listUncreatedImportRecords(
  jobId: number,
  entityType: ImportEntityType,
  afterId: number,
  limit: number,
): Promise<ImportRecordRow[]> {
  return db
    .select({ id: importRecord.id, sourceId: importRecord.sourceId })
    .from(importRecord)
    .where(
      and(
        eq(importRecord.importJobId, jobId),
        eq(importRecord.sourceEntityType, entityType),
        gt(importRecord.id, afterId),
        isNull(importRecord.localId),
      ),
    )
    .orderBy(importRecord.id)
    .limit(limit);
}

export interface CreatedImportRecordRow extends ImportRecordRow {
  // Guaranteed set by the isNotNull(localId) filter below; the column itself
  // is nullable so the query builder can't narrow it on its own.
  localId: number;
}

// The next chunk of this job's already-created issues, for the Link phase to
// fetch relations for.
export async function listCreatedImportRecords(
  jobId: number,
  entityType: ImportEntityType,
  afterId: number,
  limit: number,
): Promise<CreatedImportRecordRow[]> {
  const rows = await db
    .select({ id: importRecord.id, sourceId: importRecord.sourceId, localId: importRecord.localId })
    .from(importRecord)
    .where(
      and(
        eq(importRecord.importJobId, jobId),
        eq(importRecord.sourceEntityType, entityType),
        gt(importRecord.id, afterId),
        isNotNull(importRecord.localId),
      ),
    )
    .orderBy(importRecord.id)
    .limit(limit);
  return rows as CreatedImportRecordRow[];
}

export async function firstProjectColumnId(projectId: number): Promise<number | null> {
  const rows = await db
    .select({ id: projectColumn.id })
    .from(projectColumn)
    .where(eq(projectColumn.projectId, projectId))
    .orderBy(projectColumn.position)
    .limit(1);
  return rows[0]?.id ?? null;
}

export async function getProjectKey(projectId: number): Promise<string> {
  const [row] = await db
    .select({ key: project.key })
    .from(project)
    .where(eq(project.id, projectId));
  if (!row) throw new Error(`project ${projectId} not found`);
  return row.key;
}

export async function getIssueSequenceNumber(issueId: number): Promise<number | null> {
  const [row] = await db
    .select({ sequenceNumber: issue.sequenceNumber })
    .from(issue)
    .where(eq(issue.id, issueId));
  return row?.sequenceNumber ?? null;
}

export interface IssueTextForRewrite {
  description: string;
  comments: { id: number; body: string }[];
}

// What the Rewrite phase scans: the issue's own description plus every comment
// on it, each already local (no more Plane requests needed for this phase).
export async function getIssueTextForRewrite(issueId: number): Promise<IssueTextForRewrite> {
  const [issueRow] = await db
    .select({ description: issue.description })
    .from(issue)
    .where(eq(issue.id, issueId));
  const comments = await db
    .select({ id: issueActivity.id, body: issueActivity.body })
    .from(issueActivity)
    .where(and(eq(issueActivity.issueId, issueId), eq(issueActivity.kind, 'comment')));
  return {
    description: issueRow?.description ?? '',
    comments: comments.map((c) => ({ id: c.id, body: c.body ?? '' })),
  };
}

export async function updateIssueDescription(issueId: number, description: string): Promise<void> {
  await db.update(issue).set({ description }).where(eq(issue.id, issueId));
}

export async function updateCommentBody(commentId: number, body: string): Promise<void> {
  await db.update(issueActivity).set({ body }).where(eq(issueActivity.id, commentId));
}

// project_column has no unique constraint to upsert against like label does,
// so an exact name match within the project is looked up first and reused —
// otherwise every project's own default columns (every new project starts
// with a "Backlog") collide with Plane's own states of the same name and
// duplicate them. A match reuses the existing column's stateType rather than
// overwriting it with Plane's category: unlike a label's color, a column's
// category can move issues between board sections, so an import should not
// silently change how an existing, possibly hand-configured column behaves.
// The lookup and the insert-or-reuse share one transaction for the same
// crash-safety reason as before — a project_column still has nothing to fall
// back on for a retry once created.
export async function createLocalStateAndRecord(
  jobId: number,
  sourceId: string,
  projectId: number,
  state: CanonicalState,
): Promise<number> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: projectColumn.id })
      .from(projectColumn)
      .where(and(eq(projectColumn.projectId, projectId), eq(projectColumn.name, state.name)));
    if (existing) {
      await upsertImportRecordWith(tx, jobId, 'state', sourceId, 'state', existing.id);
      return existing.id;
    }
    const [posRow] = await tx
      .select({ pos: sql<number>`COALESCE(MAX(${projectColumn.position}), 0) + 1` })
      .from(projectColumn)
      .where(eq(projectColumn.projectId, projectId));
    const [row] = await tx
      .insert(projectColumn)
      .values({
        projectId,
        name: state.name,
        stateType: state.category,
        position: Number(posRow!.pos),
      })
      .returning({ id: projectColumn.id });
    await upsertImportRecordWith(tx, jobId, 'state', sourceId, 'state', row!.id);
    return row!.id;
  });
}

// A label of the same name already existing (created by hand before the
// import ran) is reused rather than duplicated, matching label's own
// unique(project_id, name).
export async function createLocalLabel(
  projectId: number,
  canonicalLabel: CanonicalLabel,
): Promise<number> {
  const color = canonicalLabel.color ?? '#6b7280';
  const [row] = await db
    .insert(label)
    .values({ projectId, name: canonicalLabel.name, color })
    .onConflictDoUpdate({ target: [label.projectId, label.name], set: { color } })
    .returning({ id: label.id });
  return row!.id;
}

// Same name-match-and-reuse reasoning as createLocalStateAndRecord: a cycle
// has no unique constraint to upsert against, so an existing cycle of the
// same name is reused rather than duplicated.
export async function createLocalCycleAndRecord(
  jobId: number,
  sourceId: string,
  projectId: number,
  canonicalCycle: CanonicalCycle,
): Promise<number> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: cycle.id })
      .from(cycle)
      .where(and(eq(cycle.projectId, projectId), eq(cycle.name, canonicalCycle.name)));
    if (existing) {
      await upsertImportRecordWith(tx, jobId, 'cycle', sourceId, 'cycle', existing.id);
      return existing.id;
    }
    const [row] = await tx
      .insert(cycle)
      .values({
        projectId,
        name: canonicalCycle.name,
        goal: canonicalCycle.goal ?? '',
        startDate: canonicalCycle.startDate,
        endDate: canonicalCycle.endDate,
      })
      .returning({ id: cycle.id });
    await upsertImportRecordWith(tx, jobId, 'cycle', sourceId, 'cycle', row!.id);
    return row!.id;
  });
}

export interface NewLocalIssue {
  projectId: number;
  columnId: number;
  cycleId: number | null;
  parentId: number | null;
  assigneeUserId: string | null;
  title: string;
  description: string;
  priority: string | null;
  startDate: string | null;
  dueDate: string | null;
  // The source's own ISO timestamps. Absent or unparseable leaves the column default, now().
  createdAt?: string;
  updatedAt?: string;
  archivedAt?: string | null;
}

function sourceTimestamp(value: string | undefined): Date | undefined {
  if (!value) return undefined;
  const date = new Date(value);
  return Number.isNaN(date.getTime()) ? undefined : date;
}

// An issue whose title matches one already in the project (case- and
// whitespace-insensitive, the same comparison the file-based importer uses), and
// that this job has not already mapped to another source issue, is reused
// instead of duplicated — the same protection createLocalLabel and
// createLocalStateAndRecord give their own entities. Comments and labels the
// import attaches afterward land on that existing issue rather than being
// orphaned. Skipped for a blank title: input.title falls back to
// '(untitled)' below, and matching on that fallback would merge every
// untitled Plane issue into whichever one happened to import first, which is
// not a duplicate in any real sense.
//
// Sequence numbers ("MKT-42") are issued under a row lock on project, the
// same pattern apps/api's createIssue uses, so a bulk import never collides
// with a concurrent interactive create. The import_record mapping is written
// in the same transaction as the insert: an issue has no unique constraint a
// retry could fall back on (a fresh sequence number is issued every time),
// so a crash between the insert and the mapping would otherwise duplicate
// the issue on the next attempt. sourceDisplayId (the source's own issue
// number) is stashed on that same mapping either way, for the Rewrite phase.
export async function createLocalIssueAndRecord(
  jobId: number,
  sourceId: string,
  sourceDisplayId: string,
  input: NewLocalIssue,
): Promise<number> {
  return db.transaction(async (tx) => {
    if (input.title.trim()) {
      const [existing] = await tx
        .select({ id: issue.id })
        .from(issue)
        .where(
          and(
            eq(issue.projectId, input.projectId),
            sql`lower(btrim(${issue.title})) = lower(btrim(${input.title}))`,
            notMappedByJob(jobId, 'issue', issue.id),
          ),
        )
        .orderBy(issue.id)
        .limit(1);
      if (existing) {
        await upsertImportRecordWith(
          tx,
          jobId,
          'issue',
          sourceId,
          'issue',
          existing.id,
          sourceDisplayId,
        );
        return existing.id;
      }
    }
    const [seqRow] = await tx
      .update(project)
      .set({ nextSequence: sql`next_sequence + 1` })
      .where(eq(project.id, input.projectId))
      .returning({ seq: sql<number>`next_sequence - 1` });
    const [posRow] = await tx
      .select({ pos: sql<number>`COALESCE(MAX(${issue.position}), 0) + 1000` })
      .from(issue)
      .where(eq(issue.columnId, input.columnId));
    const [row] = await tx
      .insert(issue)
      .values({
        projectId: input.projectId,
        sequenceNumber: Number(seqRow!.seq),
        columnId: input.columnId,
        cycleId: input.cycleId,
        parentId: input.parentId,
        assigneeUserId: input.assigneeUserId,
        title: input.title || '(untitled)',
        description: input.description,
        priority: input.priority,
        startDate: input.startDate,
        dueDate: input.dueDate,
        position: Number(posRow!.pos),
        createdAt: sourceTimestamp(input.createdAt),
        updatedAt: sourceTimestamp(input.updatedAt),
        archivedAt: input.archivedAt ? (sourceTimestamp(input.archivedAt) ?? new Date()) : null,
      })
      .returning({ id: issue.id });
    await upsertImportRecordWith(tx, jobId, 'issue', sourceId, 'issue', row!.id, sourceDisplayId);
    return row!.id;
  });
}

export async function setIssueLabels(issueId: number, labelIds: number[]): Promise<void> {
  if (labelIds.length === 0) return;
  await db
    .insert(issueLabel)
    .values(labelIds.map((labelId) => ({ issueId, labelId })))
    .onConflictDoNothing();
}

export async function addIssueWatchers(issueId: number, userIds: string[]): Promise<void> {
  if (userIds.length === 0) return;
  await db
    .insert(issueWatcher)
    .values(userIds.map((userId) => ({ issueId, userId })))
    .onConflictDoNothing();
}

// A project-wide field with the same name and type is reused, and a select field
// gets the options it lacks. One with the same name and another type is not: the
// imported values would land in columns that field never reads.
export async function createLocalCustomFieldAndRecord(
  jobId: number,
  sourceId: string,
  projectId: number,
  field: CanonicalCustomField,
): Promise<number> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: customField.id })
      .from(customField)
      .where(
        and(
          eq(customField.projectId, projectId),
          isNull(customField.issueTypeId),
          eq(customField.name, field.name),
          eq(customField.fieldType, field.fieldType),
        ),
      )
      .orderBy(customField.id)
      .limit(1);
    let fieldId = existing?.id;
    if (fieldId == null) {
      const [posRow] = await tx
        .select({ pos: sql<number>`COALESCE(MAX(${customField.position}), -1) + 1` })
        .from(customField)
        .where(and(eq(customField.projectId, projectId), isNull(customField.issueTypeId)));
      const [row] = await tx
        .insert(customField)
        .values({
          projectId,
          name: field.name,
          fieldType: field.fieldType,
          position: Number(posRow!.pos),
        })
        .returning({ id: customField.id });
      fieldId = row!.id;
    }
    if (field.options.length > 0) {
      const [posRow] = await tx
        .select({ pos: sql<number>`COALESCE(MAX(${customFieldOption.position}), -1) + 1` })
        .from(customFieldOption)
        .where(eq(customFieldOption.fieldId, fieldId));
      await tx
        .insert(customFieldOption)
        .values(
          field.options.map((value, index) => ({
            fieldId: fieldId!,
            value,
            position: Number(posRow!.pos) + index,
          })),
        )
        .onConflictDoNothing();
    }
    await upsertImportRecordWith(tx, jobId, 'custom_field', sourceId, 'custom_field', fieldId);
    return fieldId;
  });
}

// Written in the column of the field's own type. A value the issue already holds is
// kept, the way a reused issue keeps its title.
export async function setIssueCustomFieldValue(
  issueId: number,
  fieldId: number,
  value: string | number | boolean,
): Promise<void> {
  const [field] = await db
    .select({ fieldType: customField.fieldType })
    .from(customField)
    .where(eq(customField.id, fieldId));
  if (!field) return;
  if (field.fieldType === 'select') {
    const [option] = await db
      .select({ id: customFieldOption.id })
      .from(customFieldOption)
      .where(
        and(eq(customFieldOption.fieldId, fieldId), eq(customFieldOption.value, String(value))),
      );
    if (!option) return;
    const [held] = await db
      .select({ optionId: issueFieldOption.optionId })
      .from(issueFieldOption)
      .where(and(eq(issueFieldOption.issueId, issueId), eq(issueFieldOption.fieldId, fieldId)))
      .limit(1);
    if (held) return;
    await db
      .insert(issueFieldOption)
      .values({ issueId, fieldId, optionId: option.id })
      .onConflictDoNothing();
    return;
  }
  let column: Partial<typeof issueFieldValue.$inferInsert>;
  switch (field.fieldType) {
    case 'number':
      column = { valueNumber: String(value) };
      break;
    case 'boolean':
      column = { valueBool: Boolean(value) };
      break;
    case 'datetime': {
      const at = sourceTimestamp(String(value));
      if (!at) return;
      column = { valueDatetime: at };
      break;
    }
    default:
      column = { valueText: String(value) };
  }
  await db
    .insert(issueFieldValue)
    .values({ issueId, fieldId, ...column })
    .onConflictDoNothing();
}

// A checklist already on the issue with the same title, and not mapped by this job,
// is reused: a second import of the same source issue lands on the issue the first
// one created. The checklist, its items and the mapping are written together, so a
// retry never finds half of one.
export async function createLocalChecklistAndRecord(
  jobId: number,
  issueId: number,
  checklist: CanonicalChecklist,
): Promise<number> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select({ id: issueChecklist.id })
      .from(issueChecklist)
      .where(
        and(
          eq(issueChecklist.issueId, issueId),
          eq(issueChecklist.title, checklist.title),
          notMappedByJob(jobId, 'checklist', issueChecklist.id),
        ),
      )
      .orderBy(issueChecklist.id)
      .limit(1);
    if (existing) {
      await upsertImportRecordWith(
        tx,
        jobId,
        'checklist',
        checklist.sourceId,
        'checklist',
        existing.id,
      );
      return existing.id;
    }
    const [row] = await tx
      .insert(issueChecklist)
      .values({
        issueId,
        title: checklist.title,
        position: sql<number>`(select coalesce(max(${issueChecklist.position}), 0) + 1000 from ${issueChecklist} where ${eq(issueChecklist.issueId, issueId)})`,
      })
      .returning({ id: issueChecklist.id });
    if (checklist.items.length > 0) {
      await tx.insert(issueChecklistItem).values(
        checklist.items.map((item, index) => ({
          checklistId: row!.id,
          content: item.content,
          done: item.done,
          position: (index + 1) * 1000,
        })),
      );
    }
    await upsertImportRecordWith(tx, jobId, 'checklist', checklist.sourceId, 'checklist', row!.id);
    return row!.id;
  });
}

// A comment already on the issue with the same body and createdAt (the two
// fields Plane's own record carries verbatim), and not already mapped by this
// job, is reused instead of duplicated — the same reuse-by-content protection
// issues, states, and cycles get, needed here because a second import job
// resolves the parent issue to the same, already-created row and would otherwise
// re-post every comment on it.
export async function createLocalComment(
  jobId: number,
  issueId: number,
  authorUserId: string | null,
  authorName: string,
  bodyMarkdown: string,
  createdAt: Date,
  replyToId: number | null,
): Promise<number> {
  const [existing] = await db
    .select({ id: issueActivity.id })
    .from(issueActivity)
    .where(
      and(
        eq(issueActivity.issueId, issueId),
        eq(issueActivity.kind, 'comment'),
        eq(issueActivity.body, bodyMarkdown),
        eq(issueActivity.createdAt, createdAt),
        notMappedByJob(jobId, 'comment', issueActivity.id),
      ),
    )
    .orderBy(issueActivity.id)
    .limit(1);
  if (existing) return existing.id;

  const [row] = await db
    .insert(issueActivity)
    .values({
      issueId,
      kind: 'comment',
      body: bodyMarkdown,
      actorUserId: authorUserId,
      actorName: authorName,
      replyToId,
      createdAt,
    })
    .returning({ id: issueActivity.id });
  return row!.id;
}

// Sets an issue's parent the first time one resolves. Create sets parentId from
// whatever import_record knows at that moment, which misses a sub-issue processed
// before its parent exists; Link revisits every issue a second time (see runLink)
// and calls this once the parent is resolvable. The parent_id IS NULL guard makes
// a retry a no-op and never overwrites a parent Create already got right.
export async function setIssueParentIfUnset(issueId: number, parentId: number): Promise<void> {
  if (issueId === parentId) return;
  await db
    .update(issue)
    .set({ parentId })
    .where(and(eq(issue.id, issueId), isNull(issue.parentId)));
}

// issue_link's unique index is on the unordered pair + kind, so re-running the
// Link phase for an issue whose relations were already created is a no-op:
// the duplicate insert is caught and dropped rather than failing the tick.
export async function createIssueLink(
  sourceIssueId: number,
  targetIssueId: number,
  kind: string,
): Promise<void> {
  if (sourceIssueId === targetIssueId) return;
  try {
    await db.insert(issueLink).values({ sourceIssueId, targetIssueId, kind });
  } catch (err) {
    if (isUniqueViolation(err)) return;
    throw err;
  }
}

// drizzle wraps the driver error in a DrizzleQueryError and keeps the Postgres
// error in `cause`, so the code is looked for down the cause chain (the api's
// pgErrorCode does the same).
function isUniqueViolation(err: unknown): boolean {
  let current: unknown = err;
  for (let depth = 0; current && typeof current === 'object' && depth < 5; depth++) {
    if ((current as { code?: unknown }).code === '23505') return true;
    current = (current as { cause?: unknown }).cause;
  }
  return false;
}

// Only a user who already belongs to the project is matched: an import never
// adds someone to the project on its own just because their email matched a
// Plane assignee or comment author.
export async function findProjectMemberUserId(
  projectId: number,
  email: string,
): Promise<string | null> {
  const rows = await db
    .select({ userId: projectMember.userId })
    .from(projectMember)
    .innerJoin(user, eq(user.id, projectMember.userId))
    .where(and(eq(projectMember.projectId, projectId), sql`lower(${user.email}) = lower(${email})`))
    .limit(1);
  return rows[0]?.userId ?? null;
}

export interface NewLocalAttachment {
  projectId: number;
  issueId: number;
  filename: string;
  contentType: string;
  bytes: Buffer;
}

// Reuse-before-duplicate, the same shape as the other entities: an attachment
// already on this issue with the same filename is reused rather than re-uploaded
// on a second import run. Size, mime type, and project quota are all checked
// against the instance's own settings before the upload, so a file that cannot
// be stored never reaches the object store at all; the quota is checked once
// more inside the lock, right before the insert, the same two-step api's own
// upload route follows — the first check keeps a doomed-to-reject file off the
// object store, the second is the one a concurrent write can't slip past.
export async function createLocalAttachmentAndRecord(
  jobId: number,
  sourceId: string,
  input: NewLocalAttachment,
): Promise<number> {
  const filename = safeAttachmentFilename(input.filename);
  const [existing] = await db
    .select({ id: issueAttachment.id })
    .from(issueAttachment)
    .where(and(eq(issueAttachment.issueId, input.issueId), eq(issueAttachment.filename, filename)));
  if (existing) {
    await upsertImportRecordWith(db, jobId, 'attachment', sourceId, 'attachment', existing.id);
    return existing.id;
  }

  const limits = await getStorageSettings();
  if (input.bytes.length > limits.maxAttachmentMb * MB) {
    throw new AttachmentRejectedError(
      `"${filename}" is ${Math.ceil(input.bytes.length / MB)} MB, over the ${limits.maxAttachmentMb} MB limit`,
    );
  }
  if (!mimeAllowed(input.contentType, limits.attachmentMimeTypes)) {
    throw new AttachmentRejectedError(
      `"${filename}" is type "${input.contentType}", not accepted on this instance`,
    );
  }
  if (limits.projectQuotaMb > 0) {
    const used = await projectStoredBytes(input.projectId);
    if (used + input.bytes.length > limits.projectQuotaMb * MB) {
      throw new AttachmentRejectedError(
        `the project has used its ${limits.projectQuotaMb} MB storage quota`,
      );
    }
  }

  const key = attachmentObjectKey(input.projectId, 'attachments', input.issueId, filename);
  await putObject(key, input.bytes, input.contentType);

  try {
    return await db.transaction(async (tx) => {
      await lockAttachmentStorage(tx, input.projectId);
      if (limits.projectQuotaMb > 0) {
        // Re-checked against the lock: the check above ran before the upload,
        // outside any lock, so a concurrent write could have landed since.
        const used = await projectStoredBytes(input.projectId, tx);
        if (used + input.bytes.length > limits.projectQuotaMb * MB) {
          throw new AttachmentRejectedError(
            `the project has used its ${limits.projectQuotaMb} MB storage quota`,
          );
        }
      }
      const [row] = await tx
        .insert(issueAttachment)
        .values({
          issueId: input.issueId,
          s3Key: key,
          filename,
          contentType: input.contentType,
          sizeBytes: input.bytes.length,
        })
        .returning({ id: issueAttachment.id });
      await upsertImportRecordWith(tx, jobId, 'attachment', sourceId, 'attachment', row!.id);
      return row!.id;
    });
  } catch (error) {
    await deleteObject(key).catch(() => {});
    throw error;
  }
}