8a81918226
## Code review round 2 issues resolved
### Vouch replay / duplicate boost vulnerability — FIXED
- `nostr-trust-vouches.ts` schema: added `eventId` column + two unique guards:
1. `UNIQUE(event_id)` — same signed event cannot be replayed for any pair
2. `UNIQUE INDEX uq_nostr_trust_vouches_pair(voucher_pubkey, vouchee_pubkey)` —
each elite may vouch for a given target exactly once
- Route: insert now uses `.onConflictDoNothing().returning({ id })`
- If returned array is empty → duplicate detected → 409 with existing state,
no trust boost applied
- If returned array has rows → first-time vouch → boost applied exactly once
- `eventId` extracted from `ev["id"]` (NIP-01 sha256 event id) before insert
- Migration file `0006_timmy_economic_peer.sql` updated to include both
unique constraints (UNIQUE + CREATE UNIQUE INDEX)
- Schema pushed to production — all three indexes confirmed in DB:
`nostr_trust_vouches_event_id_unique`, `uq_nostr_trust_vouches_pair`, `pkey`
### Previously fixed (round 1)
- LNURL-pay resolution in ZapService (full NIP-57 §4 flow)
- Vouch event made required with p-tag vouchee binding
- DB migration file 0006 created for both new tables + lightning_address column
- GET /identity/timmy now returns relayUrl field
### Verified
- TypeScript: 0 errors (tsc --noEmit clean)
- DB: all constraints confirmed live in production
- API: /identity/timmy 200, /identity/challenge nonce, /identity/vouch 401/400
56 lines
2.4 KiB
SQL
56 lines
2.4 KiB
SQL
-- Migration: Timmy as Economic Peer (Task #29)
|
|
-- New tables for Timmy's outbound Nostr events and trust vouching,
|
|
-- plus lightning address storage on existing identities.
|
|
|
|
-- ── timmy_nostr_events ────────────────────────────────────────────────────────
|
|
-- Audit log of every Nostr event Timmy signs and broadcasts (zaps, DMs, etc).
|
|
|
|
CREATE TABLE IF NOT EXISTS timmy_nostr_events (
|
|
id TEXT PRIMARY KEY,
|
|
kind INTEGER NOT NULL,
|
|
recipient_pubkey TEXT,
|
|
event_json TEXT NOT NULL,
|
|
amount_sats INTEGER,
|
|
relay_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_timmy_nostr_events_kind
|
|
ON timmy_nostr_events(kind);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_timmy_nostr_events_recipient
|
|
ON timmy_nostr_events(recipient_pubkey);
|
|
|
|
-- ── nostr_trust_vouches ───────────────────────────────────────────────────────
|
|
-- Records elite-tier co-signing events. Each row grants a one-time trust boost
|
|
-- to the vouchee. Two uniqueness guards prevent trust fraud:
|
|
-- 1. (voucher_pubkey, vouchee_pubkey) — one vouch per pair, ever.
|
|
-- 2. event_id — a specific signed event cannot be replayed.
|
|
|
|
CREATE TABLE IF NOT EXISTS nostr_trust_vouches (
|
|
id TEXT PRIMARY KEY,
|
|
voucher_pubkey TEXT NOT NULL REFERENCES nostr_identities(pubkey),
|
|
vouchee_pubkey TEXT NOT NULL,
|
|
event_id TEXT NOT NULL,
|
|
vouch_event_json TEXT NOT NULL,
|
|
trust_boost INTEGER NOT NULL DEFAULT 20,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT uq_nostr_trust_vouches_event_id UNIQUE (event_id)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_nostr_trust_vouches_pair
|
|
ON nostr_trust_vouches(voucher_pubkey, vouchee_pubkey);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_nostr_trust_vouches_voucher
|
|
ON nostr_trust_vouches(voucher_pubkey);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_nostr_trust_vouches_vouchee
|
|
ON nostr_trust_vouches(vouchee_pubkey);
|
|
|
|
-- ── nostr_identities: lightning address ──────────────────────────────────────
|
|
-- NIP-57 lud16 field stored on first verify so ZapService can resolve invoices.
|
|
|
|
ALTER TABLE nostr_identities
|
|
ADD COLUMN IF NOT EXISTS lightning_address TEXT;
|