claudedo-online/server/utils/repo.ts

import postgres from "postgres";
import { randomUUID } from "node:crypto";

type Sql = ReturnType<typeof postgres>;

export interface ListRow {
  id: string;
  name: string;
  owner_id: string | null;
}

export interface TaskRow {
  id: string;
  list_id: string;
  title: string;
  description: string | null;
  source: string;
  consumed: boolean;
  owner_id: string | null;
  created_at: Date;
  updated_at: Date;
}

// Ownership model: every read/write is scoped to the caller's Zitadel sub (ownerId).
// owner_id NULL = legacy row from before multi-user — visible to every authorized user
// and adopted (stamped with the caller's sub) by the caller's next write that touches it.
// Upserts use `on conflict … where` so they can never modify a row owned by someone else.

export async function getLists(sql: Sql, ownerId: string): Promise<ListRow[]> {
  return sql<ListRow[]>`
    select id, name, owner_id from lists
    where owner_id = ${ownerId} or owner_id is null
    order by name`;
}

/** Full-replace of the caller's catalog: upsert all supplied, delete the caller's (and legacy) lists not present (cascades tasks). */
export async function replaceLists(
  sql: Sql,
  ownerId: string,
  lists: { id: string; name: string }[],
): Promise<void> {
  await sql.begin(async (tx) => {
    for (const l of lists) {
      await tx`
        insert into lists (id, name, owner_id, updated_at)
        values (${l.id}, ${l.name}, ${ownerId}, now())
        on conflict (id) do update set name = excluded.name, owner_id = ${ownerId}, updated_at = now()
        where lists.owner_id = ${ownerId} or lists.owner_id is null`;
    }
    const ids = lists.map((l) => l.id);
    if (ids.length) {
      await tx`delete from lists where (owner_id = ${ownerId} or owner_id is null) and id <> all(${ids})`;
    } else {
      await tx`delete from lists where owner_id = ${ownerId} or owner_id is null`;
    }
  });
}

export async function listExists(sql: Sql, ownerId: string, id: string): Promise<boolean> {
  const rows = await sql`
    select 1 from lists where id = ${id} and (owner_id = ${ownerId} or owner_id is null)`;
  return rows.length > 0;
}

export async function getTasksForList(sql: Sql, ownerId: string, listId: string): Promise<TaskRow[]> {
  return sql<TaskRow[]>`
    select * from tasks
    where list_id = ${listId} and (owner_id = ${ownerId} or owner_id is null)
    order by created_at`;
}

export async function createWebTask(
  sql: Sql,
  ownerId: string,
  t: { listId: string; title: string; description: string | null },
): Promise<TaskRow> {
  const id = randomUUID();
  const [row] = await sql<TaskRow[]>`
    insert into tasks (id, list_id, title, description, source, consumed, owner_id)
    values (${id}, ${t.listId}, ${t.title}, ${t.description}, 'web', false, ${ownerId})
    returning *`;
  return row;
}

/** Idempotent upsert of a desktop Idle task by id. Returns whether a row was inserted. */
export async function upsertDesktopTask(
  sql: Sql,
  ownerId: string,
  id: string,
  t: { listId: string; title: string; description?: string | null },
): Promise<{ created: boolean }> {
  const [row] = await sql<{ created: boolean }[]>`
    insert into tasks (id, list_id, title, description, source, owner_id)
    values (${id}, ${t.listId}, ${t.title}, ${t.description ?? null}, 'desktop', ${ownerId})
    on conflict (id) do update set
      list_id = excluded.list_id,
      title = excluded.title,
      description = excluded.description,
      owner_id = ${ownerId},
      updated_at = now()
    where tasks.owner_id = ${ownerId} or tasks.owner_id is null
    returning (xmax = 0) as created`;
  // row is undefined when the id exists but belongs to another user: blocked no-op.
  return { created: row?.created ?? false };
}

/**
 * Full-replace of the caller's desktop-owned partition (their current Idle backlog).
 * Mirrors `replaceLists`: upsert every supplied task as desktop-owned (consumed=true), and
 * delete any of the caller's (or legacy) desktop-owned tasks not in the payload. Web-created
 * tasks still awaiting pull (consumed=false) and other users' rows are left untouched.
 */
export async function mirrorDesktopTasks(
  sql: Sql,
  ownerId: string,
  items: { id: string; listId: string; title: string; description?: string | null }[],
): Promise<void> {
  await sql.begin(async (tx) => {
    for (const t of items) {
      await tx`
        insert into tasks (id, list_id, title, description, source, consumed, owner_id)
        values (${t.id}, ${t.listId}, ${t.title}, ${t.description ?? null}, 'desktop', true, ${ownerId})
        on conflict (id) do update set
          list_id = excluded.list_id,
          title = excluded.title,
          description = excluded.description,
          source = 'desktop',
          consumed = true,
          owner_id = ${ownerId},
          updated_at = now()
        where tasks.owner_id = ${ownerId} or tasks.owner_id is null`;
    }
    const ids = items.map((t) => t.id);
    if (ids.length) {
      await tx`delete from tasks
        where consumed = true and (owner_id = ${ownerId} or owner_id is null) and id <> all(${ids})`;
    } else {
      await tx`delete from tasks where consumed = true and (owner_id = ${ownerId} or owner_id is null)`;
    }
  });
}

/** The caller's web-created tasks the desktop has not yet imported. */
export async function getUnconsumed(sql: Sql, ownerId: string): Promise<TaskRow[]> {
  return sql<TaskRow[]>`
    select * from tasks
    where source = 'web' and consumed = false and (owner_id = ${ownerId} or owner_id is null)
    order by created_at`;
}

export async function consume(sql: Sql, ownerId: string, id: string): Promise<boolean> {
  const res = await sql`
    update tasks set consumed = true, updated_at = now()
    where id = ${id} and (owner_id = ${ownerId} or owner_id is null)`;
  return res.count > 0;
}

export async function deleteTask(sql: Sql, ownerId: string, id: string): Promise<void> {
  await sql`delete from tasks where id = ${id} and (owner_id = ${ownerId} or owner_id is null)`;
}