Reset Database for New Customer in MYLINEHUB (Safe Multi-Tenant Cleanup)

MYLINEHUB is multi-tenant. That means the same database can store many organizations safely — only if resets are done correctly.

This guide shows how to safely remove one organization’s data (customers, WhatsApp history, inventories, campaign links, etc.) without breaking the schema or impacting other tenants.

Goal: Onboard a new customer / organization on the same server with a clean dataset, while keeping your platform stable and repeatable.

Before You Delete Anything

• Take a backup (at least schema + critical tables).
• Confirm the organization key you will delete (exact spelling/case).
• Stop running jobs (campaign runners, WhatsApp webhooks, AI jobs) for that org.
• Verify dependencies: some tables depend on customers via customer_id.

In your schema, customer_property_inventory and customer_franchise_inventory are One-to-One with Customers and use customer_id. Also, customer_to_campaign maps customers to campaigns. These must be cleared before deleting from customers.

Step 1 — Decide Reset Mode

Option A: Delete only customer-side data (common)

Use this when you want to wipe leads/customers + their histories/inventories, but keep system configuration (products, users, IVRs, queues, pricing, etc.).

Option B: Full organization wipe (advanced)

Use this when you also want to wipe org-level configs such as campaigns, templates, WhatsApp registrations, settings, etc. (Only do this if your schema stores those with organization columns.)

Step 2 — Backup (Recommended)

Backup the full database:

pg_dump -U postgres -F c -d mylinehub -f mylinehub_backup_$(date +%F).dump

Or backup only one organization’s core tables (fast safety net):

-- Example: export customers for one org (CSV)
\copy (SELECT * FROM customers WHERE organization='{{your_organization}}') TO 'customers_{{your_organization}}.csv' CSV HEADER;

Step 3 — Dry Run (Count What Will Be Deleted)

Before deletion, confirm record counts. This prevents wrong-org wipes.

-- Replace with your org key
SELECT COUNT(*) AS customers
FROM customers
WHERE organization = '{{your_organization}}';

-- Inventory tables are linked by customer_id
SELECT COUNT(*) AS property_inventory_rows
FROM customer_property_inventory
WHERE customer_id IN (
  SELECT id FROM customers WHERE organization = '{{your_organization}}'
);

SELECT COUNT(*) AS franchise_inventory_rows
FROM customer_franchise_inventory
WHERE customer_id IN (
  SELECT id FROM customers WHERE organization = '{{your_organization}}'
);

-- Campaign mapping (customer_to_campaign has organization column too)
SELECT COUNT(*) AS customer_campaign_links
FROM customer_to_campaign
WHERE organization = '{{your_organization}}';

-- WhatsApp chats are org-based (as per your note)
SELECT COUNT(*) AS whatsapp_chats
FROM whats_app_chat_history
WHERE organization = '{{your_organization}}';

Step 4 — Safe Delete Order (Very Important)

Delete in this order to avoid foreign key violations and orphan rows:

1. Child tables by customer_id (inventories, attachments, AI outputs, etc.)
2. Mapping tables (customer_to_campaign)
3. Org-based history tables (WhatsApp chat history, logs, etc.)
4. Parent table: customers

Why? Because customers is referenced by other tables. If you delete customers first, dependent tables can break.

Step 5 — Run the Reset SQL (Transaction Safe)

Use a transaction so you can rollback if you notice something wrong.

BEGIN;

-- 1) Delete property inventory for this organization (by customer_id)
DELETE FROM public.customer_property_inventory
WHERE customer_id IN (
  SELECT id FROM public.customers
  WHERE organization = '{{your_organization}}'
);

-- 2) Delete franchise inventory for this organization (by customer_id)
DELETE FROM public.customer_franchise_inventory
WHERE customer_id IN (
  SELECT id FROM public.customers
  WHERE organization = '{{your_organization}}'
);

-- 3) Delete customer-to-campaign mappings for this organization
-- (Your entity includes organization column)
DELETE FROM public.customer_to_campaign
WHERE organization = '{{your_organization}}';

-- 4) Delete WhatsApp chat history for this organization
DELETE FROM public.whats_app_chat_history
WHERE organization = '{{your_organization}}';

-- 5) Finally delete customers for this organization
DELETE FROM public.customers
WHERE organization = '{{your_organization}}';

COMMIT;

If anything looks wrong: replace COMMIT with ROLLBACK.

Step 6 — Verify Reset Worked

SELECT COUNT(*) FROM customers WHERE organization='{{your_organization}}';

SELECT COUNT(*) FROM customer_property_inventory
WHERE customer_id IN (SELECT id FROM customers WHERE organization='{{your_organization}}');

SELECT COUNT(*) FROM customer_franchise_inventory
WHERE customer_id IN (SELECT id FROM customers WHERE organization='{{your_organization}}');

SELECT COUNT(*) FROM customer_to_campaign WHERE organization='{{your_organization}}';

SELECT COUNT(*) FROM whats_app_chat_history WHERE organization='{{your_organization}}';

All should return 0 after reset.

Common Mistakes to Avoid

• Deleting customers first → breaks inventory/mapping relationships.
• Wrong organization key → always dry-run counts first.
• Forgetting mapping tables like customer_to_campaign → old campaign links may create confusing UI behavior.
• Not stopping active services → background jobs can re-insert data during reset.

Optional: Make Reset Faster for Large Data

• Ensure indexes exist on customers.organization (you already have it).
• For huge orgs, delete in batches using LIMIT via IDs (advanced).
• Consider partitioning big history tables by organization (future scaling).

Final Checklist

1. ✅ Confirm organization name
2. ✅ Stop jobs / campaigns / webhooks for org
3. ✅ Dry-run counts
4. ✅ Delete in correct order (child → parent)
5. ✅ Verify counts after delete
6. ✅ Restart services and test fresh onboarding

Once reset is complete, you can onboard the new customer cleanly with a fresh dataset — without rebuilding servers or reinstalling MYLINEHUB.