byGalax
1114 lines
35 KiB
Markdown
1114 lines
35 KiB
Markdown
# 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.
|