Skip to content

A full-featured CRM built with React, shadcn/ui, and Supabase.

3.8 KB
Raw Blame
import "jsr:@supabase/functions-js/edge-runtime.d.ts";
import { sql } from "https://esm.sh/[email protected]";
import { AuthMiddleware } from "../_shared/authentication.ts";
import { db } from "../_shared/db.ts";
import { supabaseAdmin } from "../_shared/supabaseAdmin.ts";
import { getCandidateNames, type WebhookPayload } from "./candidateNames.ts";

const ATTACHMENTS_BUCKET =
  Deno.env.get("VITE_ATTACHMENTS_BUCKET") || "attachments";

// The note triggers call this function when a note is updated or deleted,
// sending the note as it was before the change.
//
// Any logged-in user can call this endpoint with a made-up payload, so the
// payload is not trusted: its attachments are only files we *may* delete.
// Each of them is deleted only if no row of the database still uses it.
// At worst, a caller can delete files that nothing uses anymore.
const deleteNoteAttachments = async (req: Request) => {
  if (req.method !== "POST") {
    return jsonResponse({ error: "Method Not Allowed" }, 405);
  }

  let payload: WebhookPayload;
  try {
    payload = await req.json();
  } catch {
    return jsonResponse({ error: "Invalid JSON payload" }, 400);
  }

  const candidates = getCandidateNames(payload);
  if (candidates.length === 0) {
    return jsonResponse({ status: "skipped", reason: "no_candidate_files" });
  }

  let paths: string[];
  try {
    paths = await getUnreferencedNames(candidates);
  } catch (error) {
    console.error("Failed to check note attachment references", { error });
    return jsonResponse(
      { error: "Failed to check note attachment references" },
      500,
    );
  }

  if (paths.length === 0) {
    return jsonResponse({ status: "skipped", reason: "still_referenced" });
  }

  const { data, error } = await supabaseAdmin.storage
    .from(ATTACHMENTS_BUCKET)
    .remove(paths);

  if (error) {
    console.error("Failed to delete note attachments", { paths, error });
    return jsonResponse({ error: "Failed to delete note attachments" }, 500);
  }

  return jsonResponse({ status: "ok", deleted: data.length });
};

Deno.serve(async (req: Request) =>
  AuthMiddleware(req, async (req: Request) => deleteNoteAttachments(req)),
);

// Returns the files that no row uses anymore, among the given ones.
//
// A file counts as used when its number (`0.8262106278726917` for
// `0.8262106278726917.pdf`) appears anywhere in one of the columns listed in
// `refs`. We search for the number only, because the extension may be
// URL-encoded in a stored URL.
//
// This runs after the note change is saved, so the edited note is checked with
// its new attachments: a file the note still has is kept.
//
// Every column that can hold uploaded files MUST be listed in `refs`. Otherwise,
// a file used both by a note and by that column gets deleted when the note
// drops it.
const getUnreferencedNames = async (names: string[]): Promise<string[]> => {
  const { rows } = await sql<{ name: string }>`
    with candidates as (
      select name, substring(name from '^0\\.[0-9]+') as stem
      from jsonb_array_elements_text(${JSON.stringify(names)}::jsonb) as name
    ),
    refs as (
      select attachments::text as text from public.contact_notes
      union all select attachments::text from public.deal_notes
      union all select logo::text from public.companies
      union all select avatar::text from public.contacts
      union all select avatar::text from public.sales
      union all select config::text from public.configuration
    )
    select c.name
    from candidates c
    where not exists (
      select 1 from refs where strpos(refs.text, c.stem) > 0
    )
  `.execute(db);

  return rows.map((row) => row.name);
};

const jsonResponse = (data: unknown, status = 200) =>
  new Response(JSON.stringify(data), {
    status,
    headers: { "Content-Type": "application/json" },
  });