Runbooks

Operational playbooks for common SipSop incidents. Each runbook follows the format: Symptom → Likely cause → Steps → Verify → Escalation.

Copy-paste ready

All SQL and shell commands in this page are copy-paste ready. Replace angle-bracket placeholders like <tenant_id> with actual values.

---

1. Tenant cannot pay — past_due over 7 days

Symptom: A tenant is stuck in past_due status and the automatic retry hasn't resolved it. Customer contacts support saying they can't use the portal.

Likely cause: Stripe payment retry schedule exhausted. Card expired or declined. Tenant's billing email not receiving Stripe notifications.

Steps

Step 1 — Check Stripe

1. Open Stripe Dashboard → Customers → search by stripeCustomerId (visible in /admin/tenants/:id/overview).
2. Look at the subscription status and the invoice history.
3. If there's a failed invoice: click → "Retry payment" (Stripe dashboard button).
4. If the payment method is expired: contact the customer to update their card via the portal's billing section (Stripe billing portal link).

Step 2 — Check local tenant status

SELECT id, name, status, stripe_status, stripe_subscription_id, access_ends_at
FROM tenants
WHERE id = '<tenant_id>';

Step 3 — Force a stripe-sync to update local status after Stripe resolves

Trigger the stripe-sync BullMQ job manually:

# From API server or locally with correct env
node -e "
const { Queue } = require('bullmq');
const q = new Queue('stripe-sync', { connection: { host: 'localhost', port: 6380 } });
q.add('manual-sync', { tenantId: '<tenant_id>' });
q.close();
"

Or run from the project root:

pnpm --filter @sipsop/api worker &
# then via Redis CLI:
redis-cli -p 6380 LPUSH bull:stripe-sync:wait '{"name":"manual-sync","data":{"tenantId":"<tenant_id>"}}'

Step 4 — If Stripe resolved but DB still shows past_due, update manually

UPDATE tenants
SET status = 'active',
    stripe_status = 'active',
    updated_at = NOW()
WHERE id = '<tenant_id>';

Step 5 — Communicate with customer

Send confirmation email once access is restored.

Verify: Log in as the tenant's client_admin (or impersonate via /admin/tenants/:id/users) and confirm the past-due banner is gone.

Escalation: If Stripe shows the subscription as cancelled (not just past_due), the subscription needs to be re-created. Contact Stripe support or re-run the checkout flow.

---

2. Stripe webhook failure

Symptom: Stripe Dashboard → Webhooks → Endpoint shows failed deliveries. Tenant status not updating. Invoices not marking as paid.

Likely cause: API server is down or unreachable. Webhook signature verification failed (wrong STRIPE_WEBHOOK_SECRET). New event type SipSop doesn't handle (non-fatal — returns 200 for unhandled events).

Steps

Step 1 — Check webhook delivery in Stripe

1. Stripe Dashboard → Developers → Webhooks → click your endpoint.
2. Check the "Failed attempts" tab. Read the error response body.
3. Note the event IDs of failed deliveries.

Step 2 — Check API logs

# Azure Container Apps:
az containerapp logs show --name sipsop-api --resource-group sipsop-rg --follow

# Or Sentry — filter by 'webhook' or 'stripe'

Step 3 — Replay failed events from Stripe

1. In Stripe Dashboard → Webhooks → endpoint → click a failed event.
2. Click "Resend" (top right). Stripe will retry delivery once.
3. For multiple events: use the Stripe CLI:

stripe events resend <evt_xxxx>
stripe events resend <evt_yyyy>

Step 4 — Check idempotency

SipSop webhook handlers are idempotent — replaying the same event multiple times is safe. The handlers check existing state before updating:

• invoice.paid: finds invoice by stripe_invoice_id, only updates if status != paid
• subscription.updated: updates tenants.stripe_status unconditionally (safe)

Step 5 — If signature verification is failing

Verify the environment variable is set correctly:

# Check in Azure Key Vault
az keyvault secret show --vault-name sipsop-kv --name sipsop-stripe-webhook-secret

Compare with the signing secret shown in Stripe Dashboard → Webhooks → endpoint → "Signing secret".

Verify: After replay, check invoices table:

SELECT id, stripe_invoice_id, status, paid_at
FROM invoices
WHERE stripe_invoice_id = '<stripe_invoice_id>';

Escalation: If the API keeps returning 5xx on webhook delivery, the issue is likely a code-level bug. Check Sentry for the stack trace.

---

3. CDR sync stuck / FreePBX unreachable

Symptom: Call records haven't updated in 15+ minutes. /admin/dashboard shows 0 calls this month or stale data. BullMQ cdr-sync queue shows failed jobs.

Likely cause: FreePBX MariaDB unreachable. Credentials wrong. Network issue between API and pbx.sopinf.xyz. High load on the PBX causing slow query.

Steps

Step 1 — Test MariaDB connectivity

# From the API server / container
mysql -h pbx.sopinf.xyz -P 3306 -u <FREEPBX_DB_USER> -p<FREEPBX_DB_PASSWORD> asteriskcdrdb \
  -e "SELECT COUNT(*) FROM cdr WHERE calldate > DATE_SUB(NOW(), INTERVAL 1 HOUR);"

If connection times out: network issue. Check Azure Container Apps egress and firewall rules on the PBX host.

If connection succeeds but returns unexpected data: FreePBX configuration change. Do NOT modify FreePBX config — it's READ-ONLY.

Step 2 — Check BullMQ failed jobs

redis-cli -p 6380 LLEN bull:cdr-sync:failed
redis-cli -p 6380 LRANGE bull:cdr-sync:failed 0 4

Or via Bull Board (if deployed): navigate to /admin/queues (if Bull Board is mounted — check apps/api/src/app.ts).

Step 3 — Read the last error

The error details are in the job's failedReason field in Redis. Use the BullMQ getFailedJobs() method or inspect via Bull Board.

Step 4 — Manual re-run of CDR sync

redis-cli -p 6380 LPUSH "bull:cdr-sync:wait" \
  '{"id":"manual-1","name":"cdr-sync","data":{},"opts":{"attempts":1}}'

Or restart the worker to pick up the next scheduled run:

# Azure Container Apps
az containerapp revision restart --name sipsop-worker --resource-group sipsop-rg

Step 5 — Check for duplicate records

CDR sync uses ON CONFLICT (pbx_server_id, freepbx_uniqueid) DO NOTHING — safe to re-run multiple times without duplicating records.

Verify:

SELECT MAX(calldate) as last_cdr, COUNT(*) as today_count
FROM cdr_records
WHERE calldate > CURRENT_DATE;

Escalation: If FreePBX is truly unreachable (PBX server down), contact the network team. Never attempt to restart or reconfigure FreePBX — it serves live calls.

---

4. Reset client_admin or client_user password

Symptom: A customer can't log in and the "forgot password" email isn't being received (email delivery issue or wrong email address).

Likely cause: Wrong email address used at registration. Email service misconfigured. User's email provider blocking the email.

Steps

Step 1 — Confirm the user exists

SELECT id, email, name, role, is_active, email_verified
FROM "user"
WHERE email = 'customer@example.com';

Step 2 — If email address is wrong, update it

UPDATE "user"
SET email = 'correct@example.com',
    email_verified = false,
    updated_at = NOW()
WHERE id = '<user_id>';

Step 3 — Trigger a password reset email

Use the Better Auth admin API endpoint (if admin plugin is enabled in auth config):

curl -X POST http://localhost:3002/api/auth/admin/set-password \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <admin_session_token>" \
  -d '{"userId": "<user_id>", "newPassword": "Temp#Pass123!"}'

Alternatively, have the user go to the login page and click "Forgot password" after correcting their email.

Step 4 — If setting a temporary password, force change on next login

Better Auth doesn't have a built-in "force change on next login" flag yet. Communicate the temporary password securely to the customer and ask them to change it immediately from Settings.

Step 5 — Invalidate existing sessions

DELETE FROM session WHERE user_id = '<user_id>';

Verify: Test the login yourself by impersonating the user from /admin/tenants/:id/users.

Escalation: If Better Auth admin API isn't available or not configured, the password hash must be updated directly in the account table. The password column is stored as an argon2 hash. Use a trusted argon2 hashing tool — do not use MD5 or bcrypt.

---

5. Seller portal access lost — token expired

Symptom: A commission rep says their magic-link expired or they lost access. They cannot log in to the seller portal.

Likely cause: The invitation token expired (7-day TTL). The rep already consumed the token on a different device. The commission_reps.user_id is null (rep was never linked).

Steps

Step 1 — Check the rep's current state

SELECT cr.id, cr.name, cr.email, cr.user_id, cr.status,
       u.email AS linked_user_email,
       u.is_active AS user_active
FROM commission_reps cr
LEFT JOIN "user" u ON u.id = cr.user_id
WHERE cr.email = 'rep@example.com';

Step 2 — Check pending invitations

SELECT id, token, expires_at, used_at
FROM seller_invitations
WHERE commission_rep_id = '<rep_id>'
ORDER BY created_at DESC
LIMIT 5;

Step 3 — Re-invite the rep

From the portal: /admin/commissions/vendedores → click the rep → "Invite to portal".

This automatically invalidates existing pending tokens and creates a new one with a fresh 7-day TTL. The invitation email is sent to commission_reps.email.

Step 4 — If the rep already has a linked user (user_id is set) but lost access

The rep may have been deactivated or their session may have expired. Check:

SELECT is_active, deactivated_at FROM "user" WHERE id = '<user_id>';

If deactivated:

UPDATE "user"
SET is_active = true, deactivated_at = NULL, updated_at = NOW()
WHERE id = '<user_id>';

Then invalidate old sessions:

DELETE FROM session WHERE user_id = '<user_id>';

Step 5 — If you need to fully reset the rep's portal access

Use revokeAccess() from the admin panel to unlink the user, then re-invite:

1. /admin/commissions/vendedores → rep detail → "Revoke portal access"
2. Then → "Invite to portal" again

This sets commission_reps.user_id = null and invalidates all pending invitations.

Verify: Confirm the rep can log in via the new invitation link.

Escalation: If invitation emails aren't being delivered, check the email service configuration and send the magic-link URL directly to the rep via a secure channel.

---

6. Statement generation failed / wrong commission numbers

Symptom: A commission statement has the wrong total, or statement generation (trpc.commissions.statements.generate) threw an error.

Likely cause: Revenue event was duplicated or missing. Commission settings were changed after the billing period closed. Currency mismatch in FX conversion. Cascade assignment failed.

Steps

Step 1 — Inspect the failing statement

SELECT s.id, s.rep_id, s.status, s.period_start, s.period_end,
       s.gross_amount_cents, s.net_amount_cents, s.currency,
       cr.name AS rep_name
FROM commission_statements s
JOIN commission_reps cr ON cr.id = s.rep_id
WHERE s.id = '<statement_id>';

Step 2 — Inspect the line items

SELECT cli.*, re.description AS revenue_description, re.amount_cents AS revenue_amount
FROM commission_line_items cli
JOIN revenue_events re ON re.id = cli.revenue_event_id
WHERE cli.statement_id = '<statement_id>'
ORDER BY cli.created_at;

Step 3 — Check the attribution log

SELECT cal.*
FROM commission_attribution_log cal
WHERE cal.revenue_event_id IN (
  SELECT revenue_event_id FROM commission_line_items WHERE statement_id = '<statement_id>'
);

The attribution log is immutable — it shows exactly what assignment logic ran and why.

Step 4 — If a revenue event is missing

Check whether the Stripe invoice webhook was processed:

SELECT * FROM revenue_events
WHERE stripe_invoice_id = '<stripe_invoice_id>';

If missing, replay the invoice.paid webhook from Stripe (see Runbook #2).

Step 5 — If numbers are wrong due to a settings change

Commission settings changes don't retroactively update statements. The commission_settings effective date determines which rates apply. If a retroactive correction is needed:

1. Delete the incorrect statement (if status = draft):

DELETE FROM commission_statements WHERE id = '<statement_id>' AND status = 'draft';
DELETE FROM commission_line_items WHERE statement_id = '<statement_id>';

2. Fix the settings if needed.
3. Regenerate from the portal.

Step 6 — If correction is needed after statement is approved or paid

Do NOT delete approved/paid statements. Instead:

1. Create a manual adjustment statement with a negative line item.
2. Document in commission_statements.notes.

Verify: Re-generate the statement and compare totals with expected values.

Escalation: If the attribution logic is returning wrong results, add debug logging to commission-calculator.ts and run testCalculation with the specific revenue event inputs.

---

7. Stripe subscription out of sync

Symptom: A tenant's Stripe subscription shows active in Stripe but suspended (or vice versa) in SipSop. The stripe_status column doesn't match what Stripe shows.

Likely cause: The stripe-sync job hasn't run recently (it runs every 6 hours). A webhook was missed. Manual status change in DB without corresponding Stripe update.

Steps

Step 1 — Check the current state

SELECT id, name, status, stripe_status, stripe_subscription_id
FROM tenants
WHERE id = '<tenant_id>';

Step 2 — Check Stripe directly

1. Stripe Dashboard → Subscriptions → search by stripe_subscription_id.
2. Note the actual subscription status.

Step 3 — Trigger stripe-sync manually

The stripe-sync job in apps/api/src/jobs/ iterates all tenants and syncs their subscription status. Trigger it:

# Add to the stripe-sync queue
redis-cli -p 6380 RPUSH "bull:stripe-sync:wait" \
  '{"id":"manual-sync","name":"stripe-sync","data":{},"opts":{"attempts":1}}'

Monitor via BullMQ logs or wait for the job to complete (typically < 30 seconds).

Step 4 — If stripe-sync doesn't fix it, update manually

Match the DB state to what Stripe shows:

UPDATE tenants
SET stripe_status = '<actual_stripe_status>',
    status = CASE
      WHEN '<actual_stripe_status>' = 'active' THEN 'active'
      WHEN '<actual_stripe_status>' = 'past_due' THEN 'past_due'
      WHEN '<actual_stripe_status>' IN ('canceled', 'incomplete_expired') THEN 'cancelled'
      ELSE status
    END,
    updated_at = NOW()
WHERE id = '<tenant_id>';

Step 5 — Check billing-calc and invoice-gen jobs

These jobs must run before stripe-sync to ensure local invoice state is correct. Verify they ran successfully in BullMQ.

Verify: Reload /admin/tenants/:id/overview and confirm the status badge matches Stripe.

Escalation: If the subscription in Stripe is in a state SipSop doesn't recognize, check the Stripe event log and replay the relevant event.

---

8. Backup and restore PostgreSQL

Symptom: Data loss, accidental deletion, or DR scenario requiring restore.

Backup (production — Azure Database for PostgreSQL)

Azure Database for PostgreSQL (Flexible Server) performs automated backups:

• Full backups: weekly
• Transaction log backups: every 5 minutes
• Retention: 7 days by default (configurable up to 35 days)

To trigger a manual backup via Azure CLI:

az postgres flexible-server backup create \
  --resource-group sipsop-rg \
  --name sipsop-postgres \
  --backup-name manual-$(date +%Y%m%d%H%M)

Manual pg_dump (local dev or migration)

# From dev (connects to local Docker on port 5434)
pg_dump \
  --host localhost \
  --port 5434 \
  --username sipsop \
  --dbname sipsop \
  --format custom \
  --file sipsop-$(date +%Y%m%d).dump

# Compress with gzip for storage
pg_dump ... --format plain | gzip > sipsop-$(date +%Y%m%d).sql.gz

Restore from pg_dump

# Custom format
pg_restore \
  --host localhost \
  --port 5434 \
  --username sipsop \
  --dbname sipsop \
  --clean \
  --if-exists \
  sipsop-20260101.dump

# Plain SQL format
psql -h localhost -p 5434 -U sipsop -d sipsop < sipsop-20260101.sql

Point-in-time restore (Azure — production)

az postgres flexible-server restore \
  --resource-group sipsop-rg \
  --name sipsop-postgres-restored \
  --source-server sipsop-postgres \
  --restore-time "2026-05-10T03:00:00Z"

This creates a new server — do not restore in-place. After validating data integrity on the restored server, update DATABASE_URL in Key Vault to point to the new server.

After restore — re-apply RLS

RLS policies are database-level. They survive a standard restore, but verify:

pnpm --filter @sipsop/db db:apply-rls-policies

Use db:apply-rls-policies (policies only), not db:apply-rls — the latter also rotates the sipsop_app password, which would lock the API out of the restored database.

Verify: Run a test query as sipsop_app role to confirm RLS is active:

SET ROLE sipsop_app;
SET app.current_tenant = 'aaaaaaaa-0000-0000-0000-000000000000';
SELECT COUNT(*) FROM cdr_records; -- Should return 0 (no records for fake tenant)
RESET ROLE;

Escalation: For production restores, notify all stakeholders before starting. Restoring the DB will cause downtime. Coordinate with the Azure team if the managed server needs to be swapped.