Multi-Tenant Webhook Architecture: How to Deliver Events to Thousands of Customers

-- Top-level tenant table
CREATE TABLE accounts (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    plan        TEXT NOT NULL DEFAULT 'free',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Per-tenant API keys (for management API access)
CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id  UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    hashed_key  TEXT NOT NULL,  -- SHA-256 hash of actual key
    key_prefix  TEXT NOT NULL,  -- "hk_live_..." for display
    revoked_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Per-tenant webhook destinations (where to deliver)
CREATE TABLE destinations (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id      UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    url             TEXT NOT NULL,
    signing_secret  BYTEA,  -- AES-256-GCM encrypted
    timeout_seconds INTEGER NOT NULL DEFAULT 30,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Per-tenant events
CREATE TABLE events (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id  UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    -- ... other fields
);

// destinations store
type Store struct{ db *sql.DB }

func (s *Store) List(ctx context.Context, accountID uuid.UUID) ([]Destination, error) {
    rows, err := s.db.QueryContext(ctx, `
        SELECT id, name, url, timeout_seconds, created_at
        FROM destinations
        WHERE account_id = $1
        ORDER BY created_at DESC
    `, accountID)
    // ...
}

func (s *Store) Get(ctx context.Context, accountID, destID uuid.UUID) (*Destination, error) {
    row := s.db.QueryRowContext(ctx, `
        SELECT id, name, url, timeout_seconds, created_at
        FROM destinations
        WHERE id = $1
          AND account_id = $2  -- ALWAYS include this
    `, destID, accountID)
    // ...
}

-- Support "pending" rotation state
ALTER TABLE destinations ADD COLUMN rotating_secret BYTEA;
ALTER TABLE destinations ADD COLUMN rotation_started_at TIMESTAMPTZ;
ALTER TABLE destinations ADD COLUMN rotation_ends_at TIMESTAMPTZ;

X-GetHook-Signature: t=1714556800,v1=<new-hmac>
X-GetHook-Signature-Previous: t=1714556800,v1=<old-hmac>

CREATE TABLE custom_domains (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id          UUID NOT NULL REFERENCES accounts(id),
    domain              TEXT NOT NULL UNIQUE,
    type                TEXT NOT NULL,  -- 'portal' or 'webhook'
    status              TEXT NOT NULL DEFAULT 'pending',  -- pending, verified, active
    tls_status          TEXT NOT NULL DEFAULT 'pending',
    verification_token  TEXT NOT NULL,  -- TXT record for DNS verification
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE brand_settings (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_id      UUID NOT NULL REFERENCES accounts(id) UNIQUE,
    company_name    TEXT,
    logo_url        TEXT,
    primary_color   TEXT DEFAULT '#3b82f6',  -- hex color
    docs_title      TEXT,
    support_email   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE delivery_attempts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id        UUID NOT NULL REFERENCES events(id),
    destination_id  UUID NOT NULL REFERENCES destinations(id),
    attempt_number  INTEGER NOT NULL,
    outcome         TEXT NOT NULL,  -- success, timeout, http_4xx, http_5xx, etc.
    response_status INTEGER,
    response_body   TEXT,
    duration_ms     INTEGER,
    attempted_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

// Key generation
func GenerateAPIKey() (plaintext, prefix, hashed string) {
    raw := make([]byte, 32)
    rand.Read(raw)
    plaintext = "hk_live_" + base64.URLEncoding.EncodeToString(raw)
    prefix = plaintext[:14]  // "hk_live_XXXXXX" — enough to identify, not enough to use
    hash := sha256.Sum256([]byte(plaintext))
    hashed = hex.EncodeToString(hash[:])
    return
}

SELECT * FROM events WHERE account_id = $1

-- Critical: all per-tenant queries must hit this index
CREATE INDEX idx_events_account_time
  ON events (account_id, created_at DESC);

-- Worker poll: doesn't filter by account, filters by status+time
CREATE INDEX idx_events_queue
  ON events (status, next_attempt_at)
  WHERE status IN ('queued', 'retry_scheduled');

-- Delivery attempts by event (for timeline view)
CREATE INDEX idx_attempts_event
  ON delivery_attempts (event_id, attempted_at DESC);