Database Setup

# backend/.env
DB_USER=postgres
DB_PASSWORD=your_password
DB_URL=localhost:5432

// backend/main.go
bdb, err := db.PgxDB("bot")
adb, err := db.PgxDB("app")
pdb, err := db.PgxDB("ponder")

// Backend creates app tables
appDb := db.App(adb, appLogger)
err = appDb.CreateTables()

// Backend creates bot tables
botDb := db.Bot(bdb)
err = botDb.CreateTables(defaultAdminId)

// Ponder creates its own tables
ponderDb := db.Ponder(pdb, appLogger)
err = ponderDb.Ping()

CREATE TABLE users (
  id TEXT PRIMARY KEY,                -- Privy DID
  contact_name TEXT,
  contact_email TEXT,
  contact_phone TEXT,
  paypal_eth TEXT,                    -- PayPal withdrawal address
  is_admin BOOLEAN DEFAULT FALSE,
  is_merchant BOOLEAN DEFAULT FALSE,
  is_organizer BOOLEAN DEFAULT FALSE,
  is_improver BOOLEAN DEFAULT FALSE,
  is_proposer BOOLEAN DEFAULT FALSE,
  is_voter BOOLEAN DEFAULT FALSE,
  is_issuer BOOLEAN DEFAULT FALSE,
  is_supervisor BOOLEAN DEFAULT FALSE,
  is_affiliate BOOLEAN DEFAULT FALSE,
  last_redemption INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE workflows (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  proposer_id TEXT REFERENCES users(id),
  supervisor_id TEXT REFERENCES users(id),
  status TEXT,  -- pending, approved, in_progress, completed, paid_out
  start_at TIMESTAMP,
  end_at TIMESTAMP,
  recurrence TEXT,  -- null, daily, weekly, monthly
  series_id TEXT,   -- Shared by recurring workflows
  is_start_blocked BOOLEAN DEFAULT FALSE,
  total_votes INTEGER DEFAULT 0,
  yes_votes INTEGER DEFAULT 0,
  quorum_reached_at TIMESTAMP,
  decision TEXT,    -- null, approved, rejected, expired, admin_approve
  created_at TIMESTAMP DEFAULT NOW()
);

pending → approved → in_progress → completed → paid_out
     ↘ rejected
     ↘ expired (>14 days)
     ↘ deleted

CREATE TABLE workflow_steps (
  id TEXT PRIMARY KEY,
  workflow_id TEXT REFERENCES workflows(id),
  step_number INTEGER,
  description TEXT,
  improver_role_id TEXT,           -- Required improver role
  improver_credential_type TEXT,   -- Required credential (optional)
  hours_allocated NUMERIC,
  sfluv_allocated NUMERIC,
  status TEXT,  -- locked, available, in_progress, completed, paid_out
  claimed_by TEXT REFERENCES users(id),
  claimed_at TIMESTAMP,
  started_at TIMESTAMP,
  completed_at TIMESTAMP
);

locked → available → in_progress → completed → paid_out

-- Proposers: Create workflows
CREATE TABLE proposers (
  user_id TEXT PRIMARY KEY REFERENCES users(id),
  status TEXT,  -- pending, approved, rejected
  reason TEXT,
  approved_at TIMESTAMP
);

-- Improvers: Execute workflow steps
CREATE TABLE improvers (
  user_id TEXT PRIMARY KEY REFERENCES users(id),
  role_id TEXT UNIQUE,  -- e.g., "landscaper", "carpenter"
  status TEXT,
  reason TEXT,
  primary_rewards_account TEXT,  -- Preferred wallet address
  approved_at TIMESTAMP
);

-- Issuers: Grant credentials
CREATE TABLE issuers (
  user_id TEXT PRIMARY KEY REFERENCES users(id),
  status TEXT
);

-- Supervisors: Oversee workflows
CREATE TABLE supervisors (
  user_id TEXT PRIMARY KEY REFERENCES users(id),
  status TEXT,
  primary_rewards_account TEXT
);

CREATE TABLE credentials (
  id TEXT PRIMARY KEY,
  user_id TEXT REFERENCES users(id),
  credential_type TEXT,  -- e.g., "dpw_certified", "sfluv_verifier"
  issued_by TEXT REFERENCES users(id),
  issued_at TIMESTAMP,
  revoked_at TIMESTAMP,
  revoked_by TEXT REFERENCES users(id)
);

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  owner TEXT REFERENCES users(id),
  name TEXT,
  address TEXT,
  lat NUMERIC,
  lng NUMERIC,
  type TEXT,           -- merchant category
  description TEXT,
  hours TEXT,
  is_approved BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE wallets (
  id SERIAL PRIMARY KEY,
  owner TEXT REFERENCES users(id),
  name TEXT,
  eoa_address TEXT,        -- Owner EOA
  smart_address TEXT,      -- Smart account (if applicable)
  smart_index INTEGER,     -- Smart account index
  is_eoa BOOLEAN,
  is_redeemer BOOLEAN DEFAULT FALSE,  -- Merchant redemption permission
  is_minter BOOLEAN DEFAULT FALSE     -- SFLUV minting permission
);

CREATE TABLE w9_wallet_earnings (
  wallet_address TEXT,
  year INTEGER,
  amount_received NUMERIC DEFAULT 0,  -- Total SFLUV received
  amount_unwrapped NUMERIC DEFAULT 0, -- Total unwrapped to HONEY
  amount_subject_to_w9 NUMERIC DEFAULT 0,  -- Net taxable amount
  PRIMARY KEY (wallet_address, year)
);

CREATE TABLE events (
  id TEXT PRIMARY KEY,
  title TEXT,
  description TEXT,
  amount NUMERIC,           -- SFLUV amount per code
  codes INTEGER,            -- Number of codes to generate
  expiration INTEGER,       -- Unix timestamp
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE codes (
  id TEXT PRIMARY KEY,      -- Redeemable code
  event_id TEXT REFERENCES events(id),
  redeemed BOOLEAN DEFAULT FALSE,
  redeemed_by TEXT,         -- Wallet address
  redeemed_at TIMESTAMP
);

CREATE TABLE w9_submissions (
  id SERIAL PRIMARY KEY,
  wallet_address TEXT,
  email TEXT,
  year INTEGER,
  status TEXT,              -- pending, approved, rejected
  submitted_at TIMESTAMP DEFAULT NOW(),
  reviewed_at TIMESTAMP,
  reviewed_by TEXT          -- Admin user ID
);

CREATE TABLE transfer_event (
  id TEXT PRIMARY KEY,
  block_number BIGINT,
  transaction_hash TEXT,
  log_index INTEGER,
  "from" TEXT,              -- Sender address (lowercase)
  "to" TEXT,                -- Recipient address (lowercase)
  amount NUMERIC,           -- Token amount (wei)
  timestamp BIGINT,
  chain_id INTEGER
);

CREATE TABLE approval_event (
  id TEXT PRIMARY KEY,
  block_number BIGINT,
  transaction_hash TEXT,
  owner TEXT,
  spender TEXT,
  amount NUMERIC,
  timestamp BIGINT,
  chain_id INTEGER
);

// Ponder webhook (simplified)
fetch(`${process.env.W9_TRANSACTION_URL}`, {
  method: "POST",
  body: JSON.stringify({
    from: event.args.from,
    to: event.args.to,
    amount: event.args.amount.toString(),
    timestamp: block.timestamp
  })
})

backend/db/
├── app.go                  # AppDB struct + CreateTables
├── app_user.go             # User CRUD
├── app_workflow.go         # Workflow + step queries
├── app_wallet.go           # Wallet management
├── app_location.go         # Location queries
├── app_contact.go          # Contact book
├── app_affiliate.go        # Affiliate queries
├── app_ponder.go           # Ponder subscription management
├── app_verified_email.go   # Email verification
├── faucet_bot.go           # BotDB struct
├── w9.go                   # W9 submissions (bot DB)
├── ponder.go               # PonderDB struct
├── ponder_transactions.go  # Transfer event queries
└── ponder_w9.go            # W9 earnings tracking

// backend/db/app_user.go
func (d *AppDB) GetUserByDid(ctx context.Context, did string) (*structs.User, error) {
    query := `SELECT id, contact_name, is_admin, is_merchant, ...
              FROM users WHERE id = $1`
    
    var user structs.User
    err := d.db.QueryRow(ctx, query, did).Scan(
        &user.Id, &user.ContactName, &user.IsAdmin, ...
    )
    return &user, err
}

pg_dump -U postgres app > app_backup.sql
pg_dump -U postgres bot > bot_backup.sql
pg_dump -U postgres ponder > ponder_backup.sql

psql -U postgres -d app < app_backup.sql

psql -U postgres -c "DROP DATABASE app; CREATE DATABASE app;"
psql -U postgres -c "DROP DATABASE bot; CREATE DATABASE bot;"
psql -U postgres -c "DROP DATABASE ponder; CREATE DATABASE ponder;"