data.md

Intro

This document shall provide you with information as to how data is handled across the application. It lightly touches on the client's database schema, but focuses on the supabase database.

1. Overview

The application takes the Zero Knowledge Architecture approach. The server orchestrates access but has no way of accessing the financial data. This is achieved by Client Side Encryption. The client encrypts financial data before beacking up using its private key. The data is backed up as versioned chunks in AWS S3.

2. Glossary

Term	Meaning
Vault	A budgeting scope with its own encryption key. Every user gets one personal vault automatically; a shared vault is a second, independent vault two or more users belong to.
VaultsParticipants	A per-vault footprint of users and the vault they belong to. Each User-Vault association is a unique row.
DEK (Data Encryption Key)	The single symmetric key that encrypts a given vault's data. Generated once per vault, never changes on password/device changes.
Wrapped Key	An encrypted copy of a DEK. The server stores multiple wrapped copies per vault (password, recovery-phrase, device) but can't unwrap any.
Concept	A budget-agnostic vault that represents real-world concepts.
Chunk	A names and version unit of encrypted synced data

3. Server Schema

ERD

create table vaults (
  vault_id uuid primary key default gen_random_uuid(),
  vault_type text not null check (vault_type in ('personal', 'shared')),
  created_at timestamptz not null default now()
);

create table vault_members (
  vault_id uuid not null references vaults(vault_id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  joined_at timestamptz not null default now(),
  primary key (vault_id, user_id)
);

create table wrapped_keys (
  vault_id uuid not null references vaults(vault_id) on delete cascade,
  wrap_method text not null,        -- 'password' | 'recovery_phrase' | 'device:<device_id>'
  wrapped_dek text not null,
  updated_at timestamptz not null default now(),
  primary key (vault_id, wrap_method)
);

create table sync_metadata (
  vault_id uuid not null references vaults(vault_id) on delete cascade,
  chunk_id text not null,
  version_id bigint not null default 1,
  last_modified_by_device text,
  updated_at timestamptz not null default now(),
  primary key (vault_id, chunk_id)
);

Row Level Security is enabled for all with policies. Do not disable RLS on the tables (only thing seating btw the anon key and cross vault data access)