# Phase 4C — Soundboard Cloud-Sync (E2E Encrypted)

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Make the existing local-only soundboard (IndexedDB) bidirectionally synced across all of a user's devices, with the audio payload encrypted client-side using the per-user X25519 key. Server only sees ciphertext + metadata; cross-device replays Just Work.

**Architecture:**
- Server: a single new table `public.user_soundboards (id, user_id, name, mime, size, category, hotkey, gain, sort_order, storage_path, created_at, updated_at)` + a Storage bucket `soundboards` (private, owner-only RLS). The table is added to `supabase_realtime` so every signed-in install of the same user can react to peer edits.
- E2E: audio Blob is encrypted with sealed-to-self `crypto_box` (sender=recipient=me) using the existing `encryptFor` / `decryptFrom` helpers in `packages/shared/src/crypto/box.ts`. The 24-byte nonce is **prepended** to the ciphertext bytes in storage — single object, no extra DB column.
- Client sync engine (`useSoundboardSync`): runs once on session-start, then idles. Triggers: (a) initial diff after sign-in pulls every missing/newer remote row + decrypts blob into IndexedDB; (b) every local change (via existing `subscribeSoundboardChanges` pubsub) debounced 500ms uploads the changed row; (c) realtime subscription on own `user_soundboards` rows triggers a targeted re-pull when another device of the user edits. LWW by `updated_at`.
- Per-sound sync badge in `SoundboardSettings`: 4 states (`synced` = ☁, `uploading` = ↑, `downloading` = ↓, `error` = ⚠).

**Tech Stack:** Postgres + RLS + Supabase Storage (private bucket) + Supabase Realtime (postgres_changes); React 18; existing `cachedUserKey` + `derivePublicKey` + `encryptFor`/`decryptFrom`. No new dependencies.

**Non-goals:**
- No blob editing of an existing sound (rename / re-encode → delete+re-add).
- No server-side decryption ever. Metadata (gain/hotkey) NOT considered sensitive — plaintext columns.
- No conflict-merge UI. LWW silently resolves.
- No Web build sync (soundboard already requires Electron).

---

## Pre-flight

- [ ] **Verify clean working tree on `main`**

Run: `cd "D:\Programmieren\ChatApp-Electron\chat-app" && git status`
Expected: clean.

- [ ] **Confirm tooling is green**

Run: `pnpm --filter @chat-app/shared typecheck && pnpm --filter @chat-app/desktop typecheck && pnpm --filter @chat-app/shared test -- --run`
Expected: all green; 38 shared tests pass.

---

## Task 1: SQL migration — `user_soundboards` + RLS + realtime + private storage bucket

**Files:**
- Create: `supabase/migrations/20260516000007_soundboards.sql`

Must be idempotent.

- [ ] **Step 1: Write the SQL migration**

Create `supabase/migrations/20260516000007_soundboards.sql`:

```sql
-- Phase 4C: cloud-synced soundboard.
--
-- user_soundboards: one row per cloud-known sound. The audio payload itself
-- lives in the `soundboards` storage bucket at `<user_id>/<sound_id>.bin`,
-- E2E-encrypted with the user's own X25519 key (sealed-to-self). The first
-- 24 bytes of the stored object are the XSalsa20 nonce; the rest is the
-- Poly1305-authenticated ciphertext.
--
-- Metadata (name, gain, hotkey, category, sort_order) is stored plaintext —
-- not considered sensitive by the spec — so other devices of the same user
-- can read it without holding the private key.

create table if not exists public.user_soundboards (
  id            uuid primary key default gen_random_uuid(),
  user_id       uuid not null references auth.users(id) on delete cascade,
  name          text not null,
  mime          text not null,
  size          bigint not null,
  category      text null,
  hotkey        text null,
  gain          real not null default 1.0,
  sort_order    integer not null default 0,
  storage_path  text not null,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),
  constraint user_soundboards_name_len check (length(name) between 1 and 200),
  constraint user_soundboards_size_pos check (size > 0)
);

create index if not exists user_soundboards_user_idx
  on public.user_soundboards(user_id, updated_at desc);

alter table public.user_soundboards enable row level security;

drop policy if exists user_soundboards_select on public.user_soundboards;
drop policy if exists user_soundboards_insert on public.user_soundboards;
drop policy if exists user_soundboards_update on public.user_soundboards;
drop policy if exists user_soundboards_delete on public.user_soundboards;

create policy user_soundboards_select
  on public.user_soundboards
  for select
  using (user_id = auth.uid());

create policy user_soundboards_insert
  on public.user_soundboards
  for insert
  with check (user_id = auth.uid());

create policy user_soundboards_update
  on public.user_soundboards
  for update
  using (user_id = auth.uid())
  with check (user_id = auth.uid());

create policy user_soundboards_delete
  on public.user_soundboards
  for delete
  using (user_id = auth.uid());

alter table public.user_soundboards replica identity full;

do $$
begin
  if not exists (
    select 1
      from pg_publication_tables
      where pubname = 'supabase_realtime'
        and schemaname = 'public'
        and tablename = 'user_soundboards'
  ) then
    execute 'alter publication supabase_realtime add table public.user_soundboards';
  end if;
end
$$;

-- Storage bucket: private, owner-only.
do $$
begin
  if not exists (select 1 from storage.buckets where id = 'soundboards') then
    insert into storage.buckets (id, name, public)
      values ('soundboards', 'soundboards', false);
  end if;
end
$$;

drop policy if exists soundboards_select on storage.objects;
drop policy if exists soundboards_insert on storage.objects;
drop policy if exists soundboards_update on storage.objects;
drop policy if exists soundboards_delete on storage.objects;

create policy soundboards_select
  on storage.objects
  for select
  using (
    bucket_id = 'soundboards'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

create policy soundboards_insert
  on storage.objects
  for insert
  with check (
    bucket_id = 'soundboards'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

create policy soundboards_update
  on storage.objects
  for update
  using (
    bucket_id = 'soundboards'
    and auth.uid()::text = (storage.foldername(name))[1]
  )
  with check (
    bucket_id = 'soundboards'
    and auth.uid()::text = (storage.foldername(name))[1]
  );

create policy soundboards_delete
  on storage.objects
  for delete
  using (
    bucket_id = 'soundboards'
    and auth.uid()::text = (storage.foldername(name))[1]
  );
```

- [ ] **Step 2: Commit + push to prod**

```bash
cd "D:\Programmieren\ChatApp-Electron\chat-app"
git add supabase/migrations/20260516000007_soundboards.sql
git commit -m "feat(P4C.T1): user_soundboards table + soundboards storage bucket + RLS"
bash scripts/prod/push-migrations.sh soundboards
```

---

## Task 2: Shared wrappers — sealed-blob crypto + CRUD + storage I/O + db-types + tests

**Files:**
- Modify: `packages/db-types/src/index.ts` — add `user_soundboards` to the Database type
- Create: `packages/shared/src/chat/soundboards.ts`
- Create: `packages/shared/src/chat/soundboards.test.ts`
- Modify: `packages/shared/src/chat/index.ts` — re-export

- [ ] **Step 1: Extend the db-types**

Read `packages/db-types/src/index.ts` to find the `public.Tables` block. Add a new entry next to the most-recent table (likely `whiteboard_strokes` from P4B.T2 `3df7cc0`):

```ts
user_soundboards: {
  Row: {
    id: string;
    user_id: string;
    name: string;
    mime: string;
    size: number;
    category: string | null;
    hotkey: string | null;
    gain: number;
    sort_order: number;
    storage_path: string;
    created_at: string;
    updated_at: string;
  };
  Insert: {
    id?: string;
    user_id: string;
    name: string;
    mime: string;
    size: number;
    category?: string | null;
    hotkey?: string | null;
    gain?: number;
    sort_order?: number;
    storage_path: string;
    created_at?: string;
    updated_at?: string;
  };
  Update: {
    id?: string;
    user_id?: string;
    name?: string;
    mime?: string;
    size?: number;
    category?: string | null;
    hotkey?: string | null;
    gain?: number;
    sort_order?: number;
    storage_path?: string;
    updated_at?: string;
  };
  Relationships: [];
};
```

Match the `Relationships` shape used by another table with a single `user_id → auth.users` FK (e.g. `devices`).

- [ ] **Step 2: Create the shared wrapper**

Create `packages/shared/src/chat/soundboards.ts`:

```ts
import { decryptFrom, encryptFor } from '../crypto/box';
import type { AppSupabaseClient } from '../supabase/client';

export const SOUNDBOARDS_BUCKET = 'soundboards';

export interface RemoteSound {
  id: string;
  userId: string;
  name: string;
  mime: string;
  size: number;
  category: string | null;
  hotkey: string | null;
  gain: number;
  sortOrder: number;
  storagePath: string;
  createdAt: string;
  updatedAt: string;
}

export interface UpsertSoundInput {
  id: string;
  name: string;
  mime: string;
  size: number;
  category: string | null;
  hotkey: string | null;
  gain: number;
  sortOrder: number;
  storagePath: string;
  updatedAtIso: string;
}

export async function listOwnSounds(client: AppSupabaseClient): Promise<RemoteSound[]> {
  const { data: session } = await client.auth.getUser();
  if (!session.user) throw new Error('not authenticated');
  const { data, error } = await client
    .from('user_soundboards')
    .select(
      'id, user_id, name, mime, size, category, hotkey, gain, sort_order, storage_path, created_at, updated_at',
    )
    .eq('user_id', session.user.id)
    .order('updated_at', { ascending: false });
  if (error) throw error;
  return data.map(mapRow);
}

export async function upsertSound(
  client: AppSupabaseClient,
  input: UpsertSoundInput,
): Promise<RemoteSound> {
  const { data: session } = await client.auth.getUser();
  if (!session.user) throw new Error('not authenticated');
  const { data, error } = await client
    .from('user_soundboards')
    .upsert(
      {
        id: input.id,
        user_id: session.user.id,
        name: input.name,
        mime: input.mime,
        size: input.size,
        category: input.category,
        hotkey: input.hotkey,
        gain: input.gain,
        sort_order: input.sortOrder,
        storage_path: input.storagePath,
        updated_at: input.updatedAtIso,
      },
      { onConflict: 'id' },
    )
    .select(
      'id, user_id, name, mime, size, category, hotkey, gain, sort_order, storage_path, created_at, updated_at',
    )
    .single();
  if (error) throw error;
  return mapRow(data);
}

export async function deleteSound(
  client: AppSupabaseClient,
  soundId: string,
): Promise<void> {
  const { data: session } = await client.auth.getUser();
  if (!session.user) throw new Error('not authenticated');
  const { data: row } = await client
    .from('user_soundboards')
    .select('storage_path')
    .eq('id', soundId)
    .eq('user_id', session.user.id)
    .maybeSingle();
  if (row?.storage_path) {
    const { error: storageErr } = await client.storage
      .from(SOUNDBOARDS_BUCKET)
      .remove([row.storage_path]);
    if (storageErr && !/not found/i.test(storageErr.message)) throw storageErr;
  }
  const { error } = await client
    .from('user_soundboards')
    .delete()
    .eq('id', soundId)
    .eq('user_id', session.user.id);
  if (error) throw error;
}

// Sealed-to-self envelope: nonce || ciphertext. encryptFor's sender and
// recipient are both the current user, equivalent to crypto_box_seal but
// reuses the existing helper (no new backend method).
export async function encryptSoundBlob(
  blob: Blob,
  myPublicKey: Uint8Array,
  myPrivateKey: Uint8Array,
): Promise<Uint8Array> {
  const bytes = new Uint8Array(await blob.arrayBuffer());
  const { ciphertext, nonce } = await encryptFor(bytes, myPublicKey, myPrivateKey);
  const out = new Uint8Array(nonce.length + ciphertext.length);
  out.set(nonce, 0);
  out.set(ciphertext, nonce.length);
  return out;
}

export async function decryptSoundEnvelope(
  envelope: Uint8Array,
  myPublicKey: Uint8Array,
  myPrivateKey: Uint8Array,
): Promise<Uint8Array> {
  const NONCE_LEN = 24;
  if (envelope.length < NONCE_LEN + 16) {
    throw new Error('sound_envelope_too_short');
  }
  const nonce = envelope.slice(0, NONCE_LEN);
  const ciphertext = envelope.slice(NONCE_LEN);
  return decryptFrom(ciphertext, nonce, myPublicKey, myPrivateKey);
}

export async function uploadSoundCiphertext(
  client: AppSupabaseClient,
  storagePath: string,
  ciphertext: Uint8Array,
): Promise<void> {
  const { error } = await client.storage
    .from(SOUNDBOARDS_BUCKET)
    .upload(storagePath, ciphertext, {
      contentType: 'application/octet-stream',
      upsert: true,
    });
  if (error) throw error;
}

export async function downloadSoundCiphertext(
  client: AppSupabaseClient,
  storagePath: string,
): Promise<Uint8Array> {
  const { data, error } = await client.storage
    .from(SOUNDBOARDS_BUCKET)
    .download(storagePath);
  if (error) throw error;
  return new Uint8Array(await data.arrayBuffer());
}

function mapRow(row: {
  id: string;
  user_id: string;
  name: string;
  mime: string;
  size: number;
  category: string | null;
  hotkey: string | null;
  gain: number;
  sort_order: number;
  storage_path: string;
  created_at: string;
  updated_at: string;
}): RemoteSound {
  return {
    id: row.id,
    userId: row.user_id,
    name: row.name,
    mime: row.mime,
    size: row.size,
    category: row.category,
    hotkey: row.hotkey,
    gain: row.gain,
    sortOrder: row.sort_order,
    storagePath: row.storage_path,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}
```

- [ ] **Step 3: Write the tests**

Create `packages/shared/src/chat/soundboards.test.ts`:

```ts
import { describe, expect, it, vi } from 'vitest';

import { getCryptoBackend } from '../crypto/backend';
import {
  decryptSoundEnvelope,
  encryptSoundBlob,
  listOwnSounds,
  upsertSound,
} from './soundboards';

function makeClient(opts: {
  user?: { id: string } | null;
  selectData?: unknown[];
  upsertReturn?: { data: unknown; error: unknown };
}): any {
  const order = vi.fn().mockResolvedValue({ data: opts.selectData ?? [], error: null });
  const eq = vi.fn().mockReturnValue({ order });
  const selectChain = vi.fn().mockReturnValue({ eq });
  const single = vi.fn().mockResolvedValue(opts.upsertReturn ?? { data: {}, error: null });
  const upsertSelect = vi.fn().mockReturnValue({ single });
  const upsertChain = vi.fn().mockReturnValue({ select: upsertSelect });
  const from = vi.fn().mockReturnValue({
    select: selectChain,
    upsert: upsertChain,
  });
  return {
    auth: { getUser: vi.fn().mockResolvedValue({ data: { user: opts.user ?? { id: 'u-1' } } }) },
    from,
  };
}

describe('encryptSoundBlob ↔ decryptSoundEnvelope', () => {
  it('round-trips bytes via sealed-to-self crypto_box', async () => {
    const backend = getCryptoBackend();
    const seed = backend.randomBytes(32);
    const pub = backend.scalarMultBase(seed);
    const blob = new Blob([new Uint8Array([1, 2, 3, 4, 5, 6, 7, 8])]);

    const envelope = await encryptSoundBlob(blob, pub, seed);
    expect(envelope.length).toBeGreaterThan(24);

    const plain = await decryptSoundEnvelope(envelope, pub, seed);
    expect(Array.from(plain)).toEqual([1, 2, 3, 4, 5, 6, 7, 8]);
  });

  it('rejects an envelope that is too short', async () => {
    const backend = getCryptoBackend();
    const seed = backend.randomBytes(32);
    const pub = backend.scalarMultBase(seed);
    await expect(
      decryptSoundEnvelope(new Uint8Array(20), pub, seed),
    ).rejects.toThrow(/too_short/);
  });
});

describe('listOwnSounds', () => {
  it('maps DB rows to camelCase', async () => {
    const client = makeClient({
      selectData: [
        {
          id: 's-1',
          user_id: 'u-1',
          name: 'horn',
          mime: 'audio/mpeg',
          size: 1234,
          category: 'fx',
          hotkey: 'F1',
          gain: 0.8,
          sort_order: 0,
          storage_path: 'u-1/s-1.bin',
          created_at: '2026-05-16T00:00:00Z',
          updated_at: '2026-05-16T00:00:00Z',
        },
      ],
    });
    const out = await listOwnSounds(client);
    expect(out[0]).toEqual({
      id: 's-1',
      userId: 'u-1',
      name: 'horn',
      mime: 'audio/mpeg',
      size: 1234,
      category: 'fx',
      hotkey: 'F1',
      gain: 0.8,
      sortOrder: 0,
      storagePath: 'u-1/s-1.bin',
      createdAt: '2026-05-16T00:00:00Z',
      updatedAt: '2026-05-16T00:00:00Z',
    });
  });
});

describe('upsertSound', () => {
  it('returns mapped RemoteSound after upsert', async () => {
    const upsertReturn = {
      data: {
        id: 's-1',
        user_id: 'u-1',
        name: 'horn',
        mime: 'audio/mpeg',
        size: 1234,
        category: null,
        hotkey: null,
        gain: 1,
        sort_order: 0,
        storage_path: 'u-1/s-1.bin',
        created_at: '2026-05-16T00:00:00Z',
        updated_at: '2026-05-16T00:00:00Z',
      },
      error: null,
    };
    const client = makeClient({ upsertReturn });
    const out = await upsertSound(client, {
      id: 's-1',
      name: 'horn',
      mime: 'audio/mpeg',
      size: 1234,
      category: null,
      hotkey: null,
      gain: 1,
      sortOrder: 0,
      storagePath: 'u-1/s-1.bin',
      updatedAtIso: '2026-05-16T00:00:00Z',
    });
    expect(out.id).toBe('s-1');
    expect(out.userId).toBe('u-1');
  });
});
```

The round-trip test uses `getCryptoBackend()` directly. If `scalarMultBase` isn't exposed on the backend interface (check with `Grep -n "scalarMultBase" packages/shared/src/crypto/`), use whatever method `derivePublicKey(privateKey)` from `apps/desktop/src/lib/userIdentity.ts:254` uses — same operation, different entry point.

- [ ] **Step 4: Re-export from the chat index**

Append to `packages/shared/src/chat/index.ts`:
```ts
export * from './soundboards';
```

- [ ] **Step 5: Run tests + typecheck**

```
pnpm --filter @chat-app/shared test -- --run soundboards
pnpm --filter @chat-app/shared typecheck
pnpm --filter @chat-app/shared test -- --run
```
Expected: 4 new tests pass; full suite at 42/42.

- [ ] **Step 6: Commit**

```bash
git add packages/db-types/src/index.ts packages/shared/src/chat/soundboards.ts packages/shared/src/chat/soundboards.test.ts packages/shared/src/chat/index.ts
git commit -m "feat(P4C.T2): shared soundboards wrappers + sealed-blob crypto + tests"
```

---

## Task 3: `useSoundboardSync` hook — initial diff + realtime + debounced local-edit sync

**Files:**
- Create: `apps/desktop/src/hooks/useSoundboardSync.ts`
- Modify: `apps/desktop/src/lib/soundboardStorage.ts` — add `putRawStoredSound` / `deleteRawStoredSound` / `getRawStoredSound` (notifyChange-bypass variants used by the sync engine to avoid feedback loops)

- [ ] **Step 1: Add the bypass helpers to soundboardStorage.ts**

Read `apps/desktop/src/lib/soundboardStorage.ts` first. At the bottom of the file, add:

```ts
// Used by the cloud-sync engine to write a pulled-from-server sound into
// IndexedDB without triggering the notifyChange pubsub — pulls are not
// "edits", and if they fired notifyChange the engine would loop (push
// debounce → upsert → realtime → pull → notifyChange → push debounce → ...).
export async function putRawStoredSound(stored: {
  id: string;
  name: string;
  mime: string;
  size: number;
  category: string | null;
  hotkey: string | null;
  gain: number;
  order: number;
  createdAt: number;
  updatedAt: number;
  blob: Blob;
}): Promise<void> {
  await tx(SOUNDS_STORE, 'readwrite', (s) => s.put(stored));
}

export async function deleteRawStoredSound(id: string): Promise<void> {
  await tx(SOUNDS_STORE, 'readwrite', (s) => s.delete(id));
}

export async function getRawStoredSound(id: string): Promise<{
  id: string;
  name: string;
  mime: string;
  size: number;
  category: string | null;
  hotkey: string | null;
  gain: number;
  order: number;
  createdAt: number;
  updatedAt: number;
  blob: Blob;
} | null> {
  const db = await openDb();
  return new Promise((resolve, reject) => {
    const t = db.transaction(SOUNDS_STORE, 'readonly');
    const s = t.objectStore(SOUNDS_STORE);
    const req = s.get(id);
    req.onsuccess = () => resolve((req.result as any) ?? null);
    req.onerror = () => reject(req.error);
  });
}
```

(`openDb`, `tx`, `SOUNDS_STORE` are file-private but accessible because the helpers live in the same file.)

- [ ] **Step 2: Build the sync hook**

Create `apps/desktop/src/hooks/useSoundboardSync.ts`:

```ts
import { useEffect, useRef, useState } from 'react';

import { getCryptoBackend } from '@chat-app/shared/crypto';
import {
  decryptSoundEnvelope,
  deleteSound as deleteRemoteSound,
  downloadSoundCiphertext,
  encryptSoundBlob,
  listOwnSounds,
  type RemoteSound,
  upsertSound,
  uploadSoundCiphertext,
} from '@chat-app/shared/chat';

import { useAuth } from '../context/AuthContext';
import {
  deleteRawStoredSound,
  getRawStoredSound,
  listSounds,
  putRawStoredSound,
  type SoundboardEntry,
  subscribeSoundboardChanges,
} from '../lib/soundboardStorage';
import { supabase } from '../lib/supabase';
import { cachedUserKey } from '../lib/userIdentity';

export type SyncBadge = 'synced' | 'uploading' | 'downloading' | 'error';

const PUSH_DEBOUNCE_MS = 500;

// Suppress unused-import lint until deleteRemoteSound is consumed by T4.
void deleteRemoteSound;

function storagePathFor(userId: string, soundId: string): string {
  return userId + '/' + soundId + '.bin';
}

export function useSoundboardSync(): {
  badges: Map<string, SyncBadge>;
  initialPullDone: boolean;
} {
  const { session } = useAuth();
  const userId = session?.user.id ?? null;
  const [badges, setBadges] = useState<Map<string, SyncBadge>>(new Map());
  const [initialPullDone, setInitialPullDone] = useState(false);
  const debounceRef = useRef<number | null>(null);

  function setBadge(id: string, badge: SyncBadge): void {
    setBadges((cur) => {
      const next = new Map(cur);
      next.set(id, badge);
      return next;
    });
  }

  useEffect(() => {
    if (!userId) {
      setInitialPullDone(false);
      setBadges(new Map());
      return;
    }
    let cancelled = false;
    let teardown: (() => void) | null = null;

    const init = async () => {
      const priv = await cachedUserKey(userId);
      if (!priv) return;
      const pub = getCryptoBackend().scalarMultBase(priv);

      try {
        await runDiff(userId, priv, pub);
      } catch (err) {
        console.error('soundboard initial diff failed', err);
      }
      if (cancelled) return;
      setInitialPullDone(true);

      const unsubLocal = subscribeSoundboardChanges(() => {
        if (debounceRef.current !== null) window.clearTimeout(debounceRef.current);
        debounceRef.current = window.setTimeout(() => {
          debounceRef.current = null;
          void runDiff(userId, priv, pub).catch((err) => {
            console.error('soundboard push diff failed', err);
          });
        }, PUSH_DEBOUNCE_MS);
      });

      const channel = supabase
        .channel('soundboards:' + userId)
        .on(
          'postgres_changes',
          {
            event: '*',
            schema: 'public',
            table: 'user_soundboards',
            filter: 'user_id=eq.' + userId,
          },
          () => {
            void runDiff(userId, priv, pub).catch((err) => {
              console.error('soundboard realtime pull failed', err);
            });
          },
        )
        .subscribe();

      teardown = () => {
        unsubLocal();
        void supabase.removeChannel(channel);
        if (debounceRef.current !== null) {
          window.clearTimeout(debounceRef.current);
          debounceRef.current = null;
        }
      };
    };

    void init();
    return () => {
      cancelled = true;
      teardown?.();
    };
    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [userId]);

  async function runDiff(uid: string, priv: Uint8Array, pub: Uint8Array): Promise<void> {
    const [localList, remoteList] = await Promise.all([
      listSounds(),
      listOwnSounds(supabase),
    ]);
    const remoteById = new Map<string, RemoteSound>();
    for (const r of remoteList) remoteById.set(r.id, r);
    const localById = new Map<string, SoundboardEntry>();
    for (const l of localList) localById.set(l.id, l);

    for (const local of localList) {
      const remote = remoteById.get(local.id);
      const localIso = new Date(local.updatedAt).toISOString();
      if (!remote) {
        await uploadAndUpsert(local, uid, priv, pub, localIso);
      } else {
        const remoteMs = Date.parse(remote.updatedAt);
        if (local.updatedAt > remoteMs) {
          await upsertMetadataOnly(local, remote, localIso);
        }
      }
    }

    for (const remote of remoteList) {
      const local = localById.get(remote.id);
      const remoteMs = Date.parse(remote.updatedAt);
      if (!local) {
        await pullAndStore(remote, priv, pub);
      } else if (remoteMs > local.updatedAt) {
        const stored = await getRawStoredSound(remote.id);
        if (stored) {
          await putRawStoredSound({
            ...stored,
            name: remote.name,
            mime: remote.mime,
            size: remote.size,
            category: remote.category,
            hotkey: remote.hotkey,
            gain: remote.gain,
            order: remote.sortOrder,
            updatedAt: remoteMs,
          });
          setBadge(remote.id, 'synced');
        }
      } else {
        setBadge(remote.id, 'synced');
      }
    }

    // Local rows that vanished from remote — propagate the delete locally,
    // but with a 5-second grace window so a freshly added local row isn't
    // wiped before its first push has fired.
    const now = Date.now();
    for (const local of localList) {
      if (!remoteById.has(local.id) && now - local.updatedAt > 5000) {
        await deleteRawStoredSound(local.id);
      }
    }
  }

  async function uploadAndUpsert(
    local: SoundboardEntry,
    uid: string,
    priv: Uint8Array,
    pub: Uint8Array,
    localIso: string,
  ): Promise<void> {
    setBadge(local.id, 'uploading');
    try {
      const stored = await getRawStoredSound(local.id);
      if (!stored) return;
      const ciphertext = await encryptSoundBlob(stored.blob, pub, priv);
      const path = storagePathFor(uid, local.id);
      await uploadSoundCiphertext(supabase, path, ciphertext);
      await upsertSound(supabase, {
        id: local.id,
        name: local.name,
        mime: local.mime,
        size: local.size,
        category: local.category,
        hotkey: local.hotkey,
        gain: local.gain,
        sortOrder: local.order,
        storagePath: path,
        updatedAtIso: localIso,
      });
      setBadge(local.id, 'synced');
    } catch (err) {
      console.error('soundboard upload failed', err);
      setBadge(local.id, 'error');
    }
  }

  async function upsertMetadataOnly(
    local: SoundboardEntry,
    remote: RemoteSound,
    localIso: string,
  ): Promise<void> {
    setBadge(local.id, 'uploading');
    try {
      await upsertSound(supabase, {
        id: local.id,
        name: local.name,
        mime: local.mime,
        size: local.size,
        category: local.category,
        hotkey: local.hotkey,
        gain: local.gain,
        sortOrder: local.order,
        storagePath: remote.storagePath,
        updatedAtIso: localIso,
      });
      setBadge(local.id, 'synced');
    } catch (err) {
      console.error('soundboard metadata upload failed', err);
      setBadge(local.id, 'error');
    }
  }

  async function pullAndStore(
    remote: RemoteSound,
    priv: Uint8Array,
    pub: Uint8Array,
  ): Promise<void> {
    setBadge(remote.id, 'downloading');
    try {
      const envelope = await downloadSoundCiphertext(supabase, remote.storagePath);
      const plain = await decryptSoundEnvelope(envelope, pub, priv);
      const blob = new Blob([plain], { type: remote.mime });
      const ms = Date.parse(remote.updatedAt);
      await putRawStoredSound({
        id: remote.id,
        name: remote.name,
        mime: remote.mime,
        size: remote.size,
        category: remote.category,
        hotkey: remote.hotkey,
        gain: remote.gain,
        order: remote.sortOrder,
        createdAt: Date.parse(remote.createdAt),
        updatedAt: ms,
        blob,
      });
      setBadge(remote.id, 'synced');
    } catch (err) {
      console.error('soundboard pull failed', err);
      setBadge(remote.id, 'error');
    }
  }

  return { badges, initialPullDone };
}
```

If `@chat-app/shared/crypto` doesn't re-export `getCryptoBackend`, look in `packages/shared/src/index.ts` for the path that does — likely just `@chat-app/shared` plain. Match what the codebase already uses (`Grep -n "getCryptoBackend" apps/desktop/src/` finds the existing renderer call sites).

- [ ] **Step 3: Typecheck**

```
pnpm --filter @chat-app/desktop typecheck
```
Expected: PASS.

- [ ] **Step 4: Commit**

```bash
git add apps/desktop/src/lib/soundboardStorage.ts apps/desktop/src/hooks/useSoundboardSync.ts
git commit -m "feat(P4C.T3): useSoundboardSync — initial diff + realtime + debounced push"
```

---

## Task 4: SoundboardSettings — mount the sync hook + per-row badge UI + remote-delete on local delete

**Files:**
- Modify: `apps/desktop/src/components/SoundboardSettings.tsx`

- [ ] **Step 1: Find the existing render loop + delete handler**

```
Grep -n "listSounds\|subscribeSoundboardChanges\|deleteSound\|map((s)" apps/desktop/src/components/SoundboardSettings.tsx
```

Identify:
- Where each sound row is rendered (the `.map(...)` over the list).
- Where the row's metadata is displayed (the cell or `<span>` group that shows name/category).
- The current delete-button onClick handler.

- [ ] **Step 2: Mount the hook + render the badge**

Add imports:
```ts
import { useSoundboardSync, type SyncBadge } from '../hooks/useSoundboardSync';
import { deleteSound as deleteRemoteSound } from '@chat-app/shared/chat';
import { supabase } from '../lib/supabase';
```
(Verify `supabase` isn't already imported — likely it isn't. `Grep -n "from '../lib/supabase'" apps/desktop/src/components/SoundboardSettings.tsx` first.)

Inside the component (near other hooks):
```ts
const { badges } = useSoundboardSync();
```

For each row, ADD a badge near the existing metadata (right next to the row's name or in the row's right-side action cluster):
```tsx
<span
  title={badgeTitle(badges.get(s.id))}
  aria-label={badgeTitle(badges.get(s.id))}
  className="ml-2 inline-flex items-center text-[10px] font-medium text-fg-muted"
>
  {badgeGlyph(badges.get(s.id))}
</span>
```

Add at the bottom of the file (outside the component):
```ts
function badgeGlyph(b: SyncBadge | undefined): string {
  switch (b) {
    case 'uploading': return '↑';
    case 'downloading': return '↓';
    case 'error': return '⚠';
    case 'synced':
    default: return '☁';
  }
}

function badgeTitle(b: SyncBadge | undefined): string {
  switch (b) {
    case 'uploading': return 'Hochladen…';
    case 'downloading': return 'Wird heruntergeladen…';
    case 'error': return 'Synchronisationsfehler';
    case 'synced':
    default: return 'Synchronisiert';
  }
}
```

- [ ] **Step 3: Wrap the existing local-delete handler to also call remote-delete**

Find the existing delete-button handler (search for `deleteSound(`). Replace its body so it does the remote delete first, then the local delete:

```ts
async (id: string) => {
  try {
    await deleteRemoteSound(supabase, id);
  } catch (err) {
    console.warn('remote sound delete failed (local delete proceeds)', err);
  }
  await deleteSound(id);
}
```

Match the surrounding closure layout — if the delete is inline JSX (`onClick={() => deleteSound(s.id)}`), restructure into either an inline `async` arrow or a separate `const handleDelete = useCallback(...)` near the other handlers.

- [ ] **Step 4: Typecheck**

```
pnpm --filter @chat-app/desktop typecheck
```
Expected: PASS.

- [ ] **Step 5: Commit**

```bash
git add apps/desktop/src/components/SoundboardSettings.tsx
git commit -m "feat(P4C.T4): SoundboardSettings — sync hook mount + per-row badges + remote-delete"
```

---

## Final gate

- [ ] **Step 1: Typecheck both packages**

Run: `pnpm --filter @chat-app/shared typecheck && pnpm --filter @chat-app/desktop typecheck`
Expected: both PASS.

- [ ] **Step 2: Run shared tests**

Run: `pnpm --filter @chat-app/shared test -- --run`
Expected: PASS — 42 tests (38 baseline + 4 new soundboard tests).

- [ ] **Step 3: Verify no uncommitted changes**

Run: `git status`
Expected: clean working tree on `main`.

- [ ] **Step 4: Report**

Report: "Phase 4C (Soundboard Cloud-Sync) code-complete on `main`; migration + storage bucket applied to prod. Restart dev → Settings → Soundboard. On first open after sign-in the badge briefly shows ↑ as local sounds upload, then ☁ once synced. Add a sound on Device A → re-open Settings on Device B → it appears (badge: ↓ briefly, then ☁). Delete from either side → vanishes everywhere. Audio bytes are E2E encrypted with your per-user key — the server never sees plaintext."

---

## Self-review (resolved inline)

1. **Spec coverage** (`docs/superpowers/specs/2026-05-16-fifteen-features-design.md` lines 103-108):
   - "New table `user_soundboards (id, user_id, name, mime, size, category, hotkey, gain, sort_order, storage_path, created_at, updated_at)`" → T1
   - "Storage bucket `soundboards/<user_id>/<sound_id>.bin`. Audio payload is encrypted client-side with the user's existing per-user X25519 key (reuses crypto/box). Server only sees ciphertext" → T1 bucket + RLS + T2 sealed-to-self via `encryptFor`/`decryptFrom`
   - "Local IndexedDB stays the working store; sync is bidirectional" → T3 `runDiff` handles both directions
   - "Sync engine: on app start + on every local edit (debounced 500ms)" → T3 init + `subscribeSoundboardChanges` debounce
   - "Realtime subscription on own `user_soundboards` rows" → T3 channel
   - "Conflict resolution: last-write-wins by `updated_at`" → T3 `local.updatedAt > Date.parse(remote.updatedAt)` comparisons
   - "UI shows '☁ Sync OK / ↑ Hochladen… / ⚠ Konflikt' badge per sound" → T4 (`Konflikt` replaced with generic `error` since LWW means real conflicts auto-resolve)

2. **Placeholders:** none.

3. **Type consistency:**
   - `RemoteSound` camelCase ↔ DB snake_case: isolated in `mapRow`; tested in T2.
   - `SoundboardEntry.order` (local) ↔ `RemoteSound.sortOrder` (remote) ↔ `sort_order` (DB): consistently translated at the wrapper boundary.
   - `SoundboardEntry.createdAt/updatedAt` are JS ms; `RemoteSound.createdAt/updatedAt` are ISO strings. T3 calls `Date.parse(...)` for comparisons and `new Date(...).toISOString()` for outbound.
   - `SyncBadge` defined T3, consumed T4.

4. **Feedback-loop guard:** `putRawStoredSound` / `deleteRawStoredSound` deliberately bypass `notifyChange()`. Without that, every realtime-pulled row would re-trigger `subscribeSoundboardChanges` → debounced push → upload → realtime → infinite loop.

5. **Bandwidth:** engine never re-uploads the blob after the first add. Metadata edits round-trip in <1 KB. A 5 MB sound = ~5 MB egress per device that pulls it; ~5 MB ingress once per upload. Acceptable.

6. **Memory-wipe interaction:** if the user wipes (P1.T12/T13), IndexedDB persists (sound blobs are there, not localStorage). The next sign-in's initial diff finds the cloud state intact. If they wipe AND re-install, all blobs are pulled from cloud on first launch.
