Operations Manual

1. System Overview

The Proposal Builder is a web-based sales and operations platform used by Innovate Healthcare to create and manage sponsorship proposals, track contacts and companies, manage projects, and handle invoicing. It is a static HTML/JavaScript application hosted on Netlify, backed by Supabase (database + authentication) and AWS S3 (backup storage).

Key Capabilities

• Build branded proposals (CVB, RB, HE) with an interactive rate card
• Generate and email PDF proposals directly to clients
• Track proposal status through the full sales lifecycle
• Manage contacts, companies, and prospects
• Manage project delivery details (deliverables per proposal)
• Run invoicing and payment tracking
• Webinar scheduling and calendar view
• Full automated daily backup to AWS S3
• Automated weekly Mailchimp contact sync with an emailed summary

2. Infrastructure & Services

Netlify Serverless Functions

3. Pages & Features

3.1 Home (index.html)

Dashboard / landing page after login. Top navigation shown in #main-nav is filtered to the sections the signed-in user's access group can reach.

• Operations Manual & Automation highlights — a two-box row directly under the welcome banner: a link to this manual, and a summary of what runs automatically (newsletter reminders, nightly backup, weekly Mailchimp sync — see §2 and §4.9).
• Quick Access — card grid linking to Builder, Proposals, Contacts, Companies, Orders, Editorial Calendar, Projects, and Advertising.
• Tips & How-To — short reference cards on building a first proposal, payment schedule options, invoice date tracking, and exporting proposals.

3.2 Proposal Builder (builder.html)

The core proposal creation tool.

Workflow

1. Fill in client info — name, company, contact, presenter, audience
2. Select services from the Rate Card (CVB, RB, or HE brand)
3. Investment Summary auto-calculates totals
4. Add custom line items if needed
5. Preview the proposal
6. Send Proposal — generates PDF and emails it to the client; archives the PDF URL to the proposal record
7. Send to Accounting — emails the accepted proposal with full detail to the accounting team

Key Features

• Multi-brand support: CVB / RB / HE with separate pricing
• Qty, monthly, and multi-instance service types
• Billing option selector & pricing validity statement
• PDF generation via browser print-to-PDF
• Proposal status tracking (Draft → Sent → Accepted / Declined)

Month Labels

Every scheduled month is saved with its year — Oct '26, never a bare Oct. Orders, invoices, Projects and both calendars all rely on this. A bare month on a monthly-billed proposal splits one month into two order cards (the Medtronic TAVR Portal duplicate, fixed Sep 29, 2026). The Builder normalizes months when a proposal loads, and the Projects month editor saves the full label (see §3.9).

Topic Portals — One Sponsor per Month

A topic portal can only be sold to one sponsor in a given month. A portal is any item in a brand's "Topic Portals" Rate Card section — both the built-in ones (cvb_tp_…) and ones added in Admin (rb_custom_…). In the month picker, months already held by another Accepted proposal show as booked. When a proposal with a portal is accepted, the same portal month is cleared from any Sent proposal that also has it (the item stays; only the month is removed).

3.3 Proposals (proposals.html)

Table view of all saved proposals. Filter by status, search by client name or company. Actions per row: open in builder, view archived PDFs, duplicate, delete.

Duplicate

Duplicate creates a new Draft with a new proposal number, "Copy of" added to the client first name, and the same services, pricing, dates, client, bill-to, billing plan and program notes. It deliberately does not copy the original's Projects deliverables and their statuses, payment tracking, extra payments, acceptance date, PO number or file, executed-proposal file, or Monday.com link — a copy is a new deal and starts clean.

3.4 Contacts (contacts.html)

Full contact database. Each contact has name, title, company, email, phone, LinkedIn, tags, and brand affiliation (CVB / RB / HE). Supports search, CSV import, and company linking.

Click the ✏ pencil icon on any contact row to open an inline edit modal — fields include First Name, Last Name, Title, Email, Phone, LinkedIn, and Notes. Changes save directly to Supabase and the table re-renders without a page reload.

3.5 Contact Detail (contact.html)

Individual contact record with full edit form, proposal history, and company link.

3.6 Companies (companies.html / company.html)

Company database with name, website, address, industry, notes, and associated contacts and proposals.

3.7 Orders (orders.html)

Monthly billing view showing all accepted proposals organized by their scheduled installment month. Primary tool for generating and emailing invoices.

Layout

• Bar chart — one bar per month showing total order value. Click a bar or use the month picker to drill into that month's orders.
• Order cards — each card shows the company, proposal ID, description, line items, and Order Total. Cards with an existing invoice show the invoice number, a PDF download button, and a Cancel Invoice button.
• Unscheduled section — orders without a scheduled billing date appear in a collapsible dark section at the bottom.
• Accepted / Sent toggle — switch between showing only Accepted proposals or including Sent proposals.
• Company filter — narrow the view to a specific company.

Creating Invoices

1. Select a month from the bar chart or month picker
2. Check the Create Invoice box on each order card that needs an invoice
3. The bottom action bar appears — click Create Invoices
4. Select an invoice date and confirm
5. Invoices are created in Supabase and automatically emailed to accounting as PDFs

How an Order Knows It's Invoiced

An order shows as invoiced when there's an active invoice on the same proposal with the same description — e.g. "Sep '26 — Monthly Billing". The link is the description text, not an id.

Unapplied Payment Credits

When a proposal has a payment recorded on the Payments page that has not yet been matched to a paid invoice, the order card shows a credit option below the Order Total:

• A 💰 Unapplied Payment Credit row shows the credit amount available
• Check Apply unapplied payment credit to deduct it from the invoice before creation
• A Balance Due row shows the net amount — the invoice and PDF will use this reduced figure
• If no invoice has been created yet, the credit checkbox only appears on uninvoiced cards

3.8 Invoices (invoices.html)

Invoice management generated from accepted proposals. Track paid / unpaid / cancelled status, and email invoices to clients.

Editing Invoice Amounts

Invoice amounts are stored when the invoice is created and do not automatically update if the proposal is later repriced. To correct an amount, click the amount value in the invoice row — an inline number field opens. Enter the correct amount and press Enter to save it to Supabase.

Editing an Invoice (Number, Description, Month)

Invoices → Edit changes the invoice number, client name, description and order month. It never changes the amount, paid status or payments.

• Duplicate number warning — if you change the number to one another invoice already uses, Catalyst lists that invoice (number, company, date, amount) and asks before saving. Invoices created from Orders always get the next number automatically and can't collide. (Added Sep 29, 2026, after hand-typed legacy numbers INV-2026-13674 and INV-2025-13621 were each used twice — both since corrected by accounting.)
• Description — this is what links the invoice to its order (§3.7). Change it only to match the order's current label.
• The PDF doesn't change. Re-downloaded PDFs print from the copy of the order saved when the invoice was created (order_data), so they always match what the client received.

Recording a Payment (Balance Due & Payment History)

Click an invoice's payment icon (💳) to open its Payment Details, which shows Balance Due (invoice amount minus everything received so far) and a Payment History list of every payment recorded against that invoice (date, check #, amount, with a delete button to remove a mis-entered one). Fill in Payment Date, Check/Ref #, and Payment Amount, then + Add Payment.

• Partial payments are fine — the invoice stays Unpaid and shows the remaining balance; add more payments later to bring it to zero, at which point it flips to Paid automatically.
• Overpayments — if a payment exceeds the remaining balance, the invoice is marked Paid and the extra amount is stashed as a payment credit against that client's proposal (see §3.8b "Payment Credits") for use on another invoice.
• Mark Unpaid is a manual override for correcting mistakes — it flips the status flag but does not delete any recorded payments.

3.8b Payments (payments.html)

Payment tracking and commission reporting. Shows all proposals with payment activity grouped by company, with full billing schedules, invoice status, and recorded payments.

Access Gate

The Payments page is protected by a one-time access code. On every visit:

1. The page shows a lock overlay
2. Click Send Access Code — a 6-digit code is emailed to Accounting@innovatehealthcare.com
3. Enter the code (expires in 10 minutes)
4. Access is granted for 4 hours — the page auto-locks after the session expires or the tab is closed

Billing Schedule & Invoice Status

Expanding a proposal shows a table with one row per installment. Each row shows:

• # — installment number
• Paid — checkbox to mark/unmark the invoice as paid (requires entering payment details)
• Description — installment name and Bill Date
• Amount — current amount from the proposal
• Invoice — linked invoice number, invoice date (click to edit), and payment summary

Payment Details Modal

Click any invoice row (paid, unpaid, or partially paid) to open the Payment Details modal. It shows:

• Invoice Amount and a live Balance Due (amount minus everything received so far)
• Payment History — every payment recorded against this invoice (date, check #, amount), each with a delete button for correcting a mis-entry
• A Record a Payment form (Payment Date, Check/Ref #, Payment Amount) with a + Add Payment button

Each payment is saved as its own row in the invoice_payments table (not overwritten on the invoice itself), so the invoice's original amount never changes. The row's payment summary and the "Paid" checkbox update immediately after saving. The footer's Save Invoice Amount button is separate — it only edits the invoice total (see the amount-editing warning in §3.8), never a payment.

Invoice Amount Mismatch

If a proposal was repriced after its invoice was created, an amber warning badge appears on the installment row showing the stored invoice amount vs. the current proposal amount. Click the badge to update the invoice to the current amount in one step.

Payment Credits (Overpayments)

When a payment recorded against an invoice exceeds its balance due, the leftover is stored as a payment credit tied to that client's proposal (payment_credits table) rather than being lost or forced onto the wrong invoice. A blue credit banner appears above the installment table for that proposal showing the credit amount and a dropdown of that proposal's other open invoices — pick one and click Apply to use some or all of the credit there. Applying a credit inserts it as a normal entry in that invoice's Payment History, so it follows the same partial/paid-in-full/balance rules as any other payment.

Recorded Payments (Ad-hoc)

The Recorded Payments section below the installment table allows entering payments that aren't tied to a specific invoice — useful for deposits or payments received outside the standard invoice flow. Each entry has: Date, Amount, and Check / Ref #. This is the older, proposal-level mechanism that feeds the Orders page's "Unapplied Payment Credit" (§3.7) — for a payment you already know belongs to a specific invoice, prefer recording it directly on that invoice (above) instead.

• Click + Add Payment to record a new payment
• If the proposal has exactly one unpaid invoice, or one whose amount matches, the payment is applied to that invoice's balance (partial/paid-in-full/overpayment-credit rules all apply, same as recording it directly)
• Payments recorded here feed into the Commissions PDF report

Reports

• 📄 PDF Report — date-range report of all payments received (from paid invoices + ad-hoc recorded payments), with deduplication to avoid double-counting. Set the From/To month range before generating.
• 📊 Excel Report — same data as PDF Report in .xlsx format: Company, Invoice #, Invoice Date, Payment Date, Invoice Total, Amount Paid. Filter by month range.
• 📄 Commissions — PDF commission report from ad-hoc recorded payments, grouped by presenter with commission calculations. Filter by month using the All Months dropdown.

Commission rates per presenter are set in Admin → Settings → Users / Presenters — each presenter card has a Commission % field (default 4%).

3.8c Reports (reports.html)

Company-wide revenue view: KPI cards for Contracted (accepted proposals), Invoiced (active invoices), Collected (payments received) and Outstanding (invoiced and unpaid), filterable by year and brand, plus the Revenue by Category table with its own PDF download.

Print Proposal Audit

Downloads a PDF audit of every proposal in a date range: what was sold, how it bills, what's been invoiced and paid, where delivery stands, and anything that needs attention. Use it for month-end or quarter-end reviews, or before handing a book of business to accounting.

1. Click 🧾 Print Proposal Audit (top right of Reports)
2. Optionally pick a Company — start typing and choose from the list of every company with a proposal. Leave it blank for all companies.
3. Pick a Start date and End date — defaults to Jan 1 – Dec 31 of the current year
4. Tick the statuses to include — Accepted, Completed and Sent by default; Draft and Declined are optional
5. Click Download PDF — it loads live proposals, invoices and payments and saves Catalyst-Proposal-Audit_<start>_to_<end>.pdf (with the company name added when one is picked, e.g. Catalyst-Proposal-Audit_Medtronic_2026-01-01_to_2026-12-31.pdf)

Which proposals are included: any proposal whose term (start–end date) overlaps the range. A proposal with no term is included when its proposal date falls in the range. With a company picked, only that company's proposals are included — matched on the company the proposal is listed under (the sponsoring client when there is one, as on Orders). If nothing matches, it says so instead of downloading an empty file.

What's in the PDF

• Page 1 — summary: proposal count, how many have critical issues or warnings, accepted + completed value, invoiced and paid totals, and a table of every critical issue and warning in the range.
• One section per proposal, A–Z by company: status and totals, billing plan, term, PO, then flagged issues, and tables of Items (qty, price, months), Orders (month, amount, which invoice covers it), Invoices & payments (amount, status, each payment with date, check # and fee) and Projects deliverables (status, due date, owner, months).
• Letter landscape, "Page X of Y" footer on every page.

What Gets Flagged

3.9 Projects (projects.html)

Project delivery board. Each accepted proposal becomes a project with a deliverables panel showing the Project Details bullets for each selected Rate Card section.

Key Features

• Status tracking — per-item status selector (Not Started, In Progress, Scheduled, Completed, On Hold, Cancelled, Not Using). Status column is 160px wide to accommodate labels.
• Owner assignment — assign a team member per deliverable row
• Date range — start/end date fields per deliverable
• Notes sub-row — a slim inline notes field sits below each deliverable row; hidden in print unless it has content
• Month pills — for month-based services, click the calendar icon to assign scheduled months. Month-qty items (e.g. Topic Portals selected for 3 months) display as a single row labeled "Portal Name — 3 months" rather than three separate rows. See Editing Months below.
• Group header styling — when a proposal group is expanded, the header bar turns dark navy with white text so it visually anchors the open panel
• Owner filter — filter the board by assigned owner using the toolbar dropdown
• Accepted-only toggle — show only accepted proposals
• Add to Calendar (📅) — each deliverable row has a calendar checkbox in the last column. Check it to flag the deliverable for calendar export. The default checked/unchecked state per catalog item type is stored in settings — items with a date range and a checked box can be bulk-exported to a calendar file.
• Print — print-friendly layout; empty notes rows are hidden automatically

Editing Months

Changing a month in Projects also changes it on the proposal — for single items and for each copy of a qty > 1 item — so Orders, invoices and the calendars stay in step with Projects.

• Picker — shows the proposal term plus 6 months before and 12 after. Months outside the term have a dashed outline.
• Invoiced month → blocked. On monthly-billed proposals you can't remove a pill from, or add one to, a month that has an active invoice. The message names the invoice. Change or cancel the invoice first.
• Portal already sold → blocked. A portal month held by another Accepted proposal can't be picked; the message names the sponsor.
• Outside the term → confirm. Picking a dashed month offers to extend the start or end date. It never shortens the term. On Accepted or Completed proposals the prompt warns that Catalyst's dates will no longer match the signed proposal. To shorten a term, use the Builder.
• Due dates — when you swap one month for another, it offers to move matching due dates by the same number of months (completed items are left alone).
• Sub-task progress stays with its month. Month sub-tasks are re-pointed by month when months move. A removed month's sub-tasks are set aside (key suffix __xm_{month}), not deleted.
• No overwriting — it re-reads the proposal before saving and refuses if someone else saved in the meantime ("Refresh the page and try again").
• Change log — every change is appended to the proposal's monthChangeLog (who, when, item, old → new months, any term change, due dates moved). There's no screen for it yet; it's in the data and in backups.

Advertising Items Excluded

The following advertising-only catalog sections are intentionally excluded from the Projects board — they are tracked on the Advertising page instead:

• Newsletter Banner Ads
• Website Banner Ads
• Dedicated Email Promotions

3.10 Calendar, Webinars, Advertising, Lead

Additional operational views: Calendar — webinar/event scheduling; Webinars — webinar management; Lead — lead/prospect tracking.

Sponsored Portals on the Calendar & Editorial Calendar

• A newsletter linked to a topic portal (Admin → Newsletters, portal link) turns purple and reads "Newsletter — Sponsor" in any month a non-declined proposal has that portal. It's per brand: SimZ's RB AI portal colors the RB AI newsletter, not the CVB or HealthExec one.
• On the Calendar, the sponsoring proposal's own portal row (if it has Add to Calendar checked) is hidden in that month, so the portal doesn't appear twice (red + purple).

Advertising (advertising.html)

Campaign management board showing advertising placements by section. Section order (left to right / top to bottom):

1. Dedicated Email Promotions
2. Website Banner Ads
3. Newsletters
4. Topic Portals

To reorder sections, edit the SECTIONS array in advertising.html (search for SECTIONS = [).

3.11 Tradeshow Pages (acc.html, rsna.html, etc.)

Twenty dedicated pages — one per conference — for managing exhibitor contacts at each show. Access via Admin → Tradeshows. Each page is keyed to a show tag (e.g. ACC, RSNA) that links contacts and companies to that show.

Exhibitor Table

Shows all companies registered as exhibitors for that show. Each company row lists its booth number and assigned contacts. The Booth # displays as plain text with a pencil icon — click to edit inline, then click away or press Enter to save. This keeps the table compact without a permanent input field.

Unassigned Contacts

Below the exhibitor table, the Unassigned Contacts section lists all contacts tagged with the show's tag but not yet assigned to an exhibitor row. Each contact row has four icon buttons on the right:

Contacts appear in Unassigned when they have the show tag but their company is either not an exhibitor or not yet added to that show's list. Use the 🏢 button to add the company first, then ➕ to assign.

3.12 Contact Form Embed (contact-form-embed.html)

A standalone, embeddable contact form designed to be iframed into InnovateHealthcare.com. Styled to match the Innovate site (green #2d7a3a primary, system sans-serif, white background).

Fields

• First Name, Last Name (required)
• Email, Phone
• Company (required), Job Title
• Publication of Interest — CVB / RB / HE (required)
• How Can We Help? — interest selector (required)
• Conferences of Interest — checkboxes that animate in based on the selected publication
• Additional Comments

Behaviour

• POSTs to /.netlify/functions/contact-form
• On success: form is replaced with a thank-you message inside the iframe (no page redirect)
• On error: toast notification appears inside the iframe

Embed Code

<iframe src="https://catalyst.innovatehealthcare.com/contact-form-embed.html"
  width="100%" height="700" frameborder="0"></iframe>

4. Admin Panel — Full Reference

Access: Admin (admin.html) — requires Admin group membership. The panel is divided into tabs: Rate Card, SOW, Project Details, Terms, and Settings.

4.1 Rate Card

Defines all billable services across three brands: CVB, RB, and HE.

• Sections — top-level groupings (e.g. "Digital Advertising")
• Items — individual services with name, description, price, and enabled/disabled toggle per brand
• Item types: checkbox, quantity, monthly, multi-instance
• Click Save to write to Supabase and localStorage
• 🖨 Print Proof — opens a print-ready view of all brands + Billing Options, Terms, Pricing Validity, Benefits, and Invoicing. Includes Export to Excel.

4.2 Statement of Work (SOW)

Defines the bullet points that appear in the SOW section of each proposal, organized by Rate Card section and brand. Editing CVB bullets auto-propagates to RB and HE. Changes auto-save per section. Print Proof available.

4.3 Project Details

Defines the deliverable bullet points that appear on the Projects board for each Rate Card section. Bullets have text, bold flag, and owner assignment. CVB is the master — edits propagate to RB and HE automatically. Print Proof available.

4.4 Terms

Defines the Terms & Conditions sections that appear in proposals. Each term has a Title and Body. Add / edit / reorder / delete. Print Proof available.

4.5 Settings

4.5b Newsletters (Admin Accordion)

The Newsletters accordion in Admin manages the newsletter schedule for all three brands. Each brand (CVB, RB, HE) has its own table of newsletter issues with the following columns:

• Click + Add Newsletter under any brand to add a new row
• Click Save Newsletters to write all changes to Supabase
• 🖨 Print — opens a print-ready Newsletter Schedule view for all brands
• Export to Excel — downloads IH-Newsletters.xlsx

4.5c Global Settings — Tradeshow Tasks

Within the Global Settings group in Admin, Tradeshow Tasks is a collapsible section that defines the standard task checklist template applied to each tradeshow. Tasks entered here appear as a reusable task list on each tradeshow page. Click ▼ expand to open the editor, add or reorder tasks, then save.

4.5d Global Settings — API Keys

The API Keys section within Global Settings stores third-party API credentials used by Netlify functions. Currently:

• Mailchimp — API Key — stored as mailchimp_api_key in the Supabase settings table
• Mailchimp — Data Center — stored as mailchimp_dc (e.g. us21)

These values are read server-side by contact-form.js and mailchimp-subscribe.js — they are never exposed in the browser URL. Enter keys only here; never share them in chat or email.

4.6 Contact Form Flow

Accessible at Admin → Contact Form Flow. A visual flowchart showing how submissions from the InnovateHealthcare.com contact form are processed — which email is notified, whether a Supabase contact record is created, and how Mailchimp subscriptions are applied per interest type.

Routing by Interest

Mailchimp Interest Groups

For CRM interests (Media Kit, Custom Marketing, Press Release), the function sets Mailchimp interest groups (not tags) on the IH Master List based on the Publication of Interest selected:

• CVB — Cardiovascular Business interest group
• RB — Radiology Business interest group
• HE — HealthExec interest group

For Media Kit and Custom Marketing interests, the corresponding brand list (CVB / RB / HE) also receives a subscription with newsletter interest groups (Daily News, Weekly News, Monthly News, Announcements) set as Mailchimp interest groups — not tags. The media kit request date is written to the appropriate Mailchimp merge field (MMERGE7 / MMERGE8 / MMERGE10).

4.7 Data & Backup Management

4.8 Data Cleanup

The Data Cleanup accordion in Admin contains all tools for maintaining contact and company data quality. Panels can be dragged to reorder — the order is saved to Supabase and shared across all users.

Company Duplicate Detector

Scans all companies and groups records with similar names, matching websites, or matching phone numbers. Each group is presented as a table — click a row to select the record to keep, then click Merge →. The merge operation re-points all contacts, proposals, invoices, and tradeshow registrations to the keeper record and deletes the duplicates.

Find Duplicate Companies

Groups companies by identical name (case-insensitive). Same workflow: select keeper → Merge →. Uses dbMergeCompanies() which handles all FK re-pointing and tag merging automatically.

Find Duplicate Contacts

Groups contacts by matching email (primary) or matching first + last name (secondary). Select the record to keep, click Merge →. The merge:

• Keeps the keeper's field values; fills any blanks from the duplicate records
• Unions all tags from all records
• Re-points any proposals whose clientEmail matches a duplicate → keeper's email
• Deletes the duplicate records

Auto-classify Company Statuses

Sets company status automatically from proposal history — companies with Accepted proposals become Active, companies with Sent proposals become Proposal. Only upgrades statuses; never downgrades a status that is already set higher.

Fix Broken Parent Links

Finds and removes invalid parent_company_id references — e.g. a company pointing to a parent that no longer exists. Safe to run at any time.

Company Status CSV Import

Paste a CSV with company name and status columns to bulk-set company statuses. Useful for importing status data from an external CRM or spreadsheet.

Company Parent Relationships

Tools for managing parent/child company relationships (org chart). Assign, move, or remove parent company links in bulk.

Find Missing Websites

Lists all companies that have no website on file, so they can be prioritised for Apollo enrichment or manual entry.

Link Contacts → Companies (FK Backfill)

Sets the company_id foreign key on all contacts that are linked only by text company name. Matching runs in three passes:

1. Exact — case-insensitive name match
2. Fuzzy — strips parentheticals ((CT), (Cardiology)) and legal suffixes (Inc., LLC, etc.) then matches
3. Slash/Pipe — takes the first segment of Philips / Biotel → tries Philips
4. Prefix drop — progressively removes trailing words to catch sub-brand names like Fujifilm Medical Systems USA → Fujifilm

Results are reported as: N linked (exact) · N linked (fuzzy) · N no match · N no company name set.

The no-match list renders as clickable blue chips. Click a chip to create that company and immediately link all waiting contacts to it via FK. The new company's DB-confirmed UUID is used as the FK — no orphaned links.

Apollo Enrichment — Enrich Companies

Bulk-enriches all companies via the Apollo Organization Enrich API. Fills: website, phone, address, city, state, zip, industry. Uses the company's domain (website field) if set, otherwise falls back to the company name. Processes in batches of 10 with a live progress bar. Requires an Apollo API key saved in Admin → API Keys → Company Enrichment Key.

Apollo Enrichment — Enrich Contacts

Bulk-enriches all contacts via the Apollo People Match API. Fills: title, phone, LinkedIn URL. Matches by email first; falls back to first name + last name + company name. Same batching and API key requirement as company enrichment.

Export Companies to Excel

Downloads all companies as IH-Companies.xlsx with columns: Name, Website, Phone, Address, City, State, Zip, Industry, Status, Sales Person, Tags.

Export Contacts to Excel

Downloads all contacts as IH-Contacts.xlsx with columns: First Name, Last Name, Title, Email, Phone, LinkedIn, Company, Agency, Tags.

Import Companies from Excel

Upload an .xlsx, .xls, or .csv file to bulk-create or update companies. First row must be headers. Required column: Name (also accepts "Company" or "Company Name"). Optional columns are matched case-insensitively and include common aliases (e.g. "Salesperson" → Sales Person). Behaviour on match:

• Existing company (matched by name): blank fields are filled; existing values are never overwritten; tags are unioned
• New name: a new company record is created

A 5-row preview is shown before import. Progress is displayed during import (batches of 50). Results show: N created · N updated · N skipped.

Import Contacts from Excel

Upload an .xlsx, .xls, or .csv file to bulk-create or update contacts. Required columns: First Name + Last Name. Deduplication order:

1. Email match (case-insensitive)
2. First + Last + Company match (fallback for contacts without email)

On match: blank fields filled, existing values kept, tags unioned. Company FK (company_id) is automatically resolved if the company name exists in the database. Same preview + batch import flow as Companies.

4.9 Subscription Update & Mailchimp Sync

The Subscription Update accordion in Admin (under Contacts) contains three routines, meant to be run in order:

1. Apply Tag Subscriptions to Contacts — for each company, reads its tags and the publications mapped to those tags (Company Tags), and adds the matching CVB / RB / HE subscription flag to every contact under that company. Only adds — never removes.
2. Push Subscriptions to Mailchimp — syncs contacts to each Mailchimp brand audience based on their CVB / RB / HE flags, subscribing with the standard newsletter tags. Never re-subscribes a contact who previously unsubscribed.
3. Push All Contacts to Innovate Healthcare List — syncs every contact with an email to the Innovate Marketing audience, stamping CVB_CAT / RB_CAT / HE_CAT merge fields with today's date and applying Sub: CVB / RB / HE tags based on subscription flags.

All three delegate the actual Mailchimp API calls to mailchimp-bulk-subscribe.js, which batches 5 contacts at a time and retries on 429 (rate-limit) responses.

5. Data Architecture

Supabase Tables

Key Relationships

Brand architecture: Each Rate Card section exists three times in catalog_sections — once per brand (CVB, RB, HE). Items and bullets are stored separately per brand, linked by UUID. Renaming a section updates all three brand rows automatically.

Months on proposals: scheduled months live in data.selectedServices[brand][item].months (e.g. ["Oct '26", "Nov '26"]). Each copy of a qty > 1 item can also hold its own months in data.deliverableState[key].months; the Projects editor keeps both in step. Month changes made in Projects are logged in data.monthChangeLog. annotateMonthsWithYear() keeps a saved year as-is and only infers one for legacy bare months.

Project Details propagation: When editing Project Details bullets for a CVB section, the system automatically writes identical bullets to the matching RB and HE rows so all brands stay in sync.

6. Backup & Restore Procedures

6.1 What Gets Backed Up

6.2 Automated Daily Backup

The scheduled backup runs automatically every day at 2:00 AM UTC. It fetches all 17 tables from Supabase and uploads a JSON file to S3 (always a full export), then re-uploads archived Sent/Accepted proposal PDFs and PO/executed-proposal attachments to S3, then sends a summary email to BACKUP_NOTIFY_EMAIL.

6.3 Manual Backup — Run Full Backup Now

Use after major data changes (recovery operation, bulk import, schema change), or any time you want to confirm everything is backed up right now instead of waiting for the nightly job.

1. Go to Admin → Data & Backup Management
2. Click ▶ Run Full Backup Now
3. If prompted for the Backup Secret, enter the value from Netlify env vars (BACKUP_SECRET) — saved in your browser after first entry
4. Wait for the success toast (typically 15–30 seconds)
5. A confirmation email is sent to BACKUP_NOTIFY_EMAIL

Result: JSON + Sent/Accepted PDFs + PO/executed-proposal attachments uploaded to S3. Unlike the nightly job, this always processes every proposal (a true full pass), not just what changed — and on success it advances the same incremental cursor the nightly job uses, so that run has less to redo.

6.4 Manual Backup — Export Data → S3

Faster option for a data-only snapshot (no PDF archiving, no email).

1. Go to Admin → Data & Backup Management
2. Click 📄 Export Data → S3
3. Wait for the success toast (~5–10 seconds)

When to use: After catalog/settings changes, before a restore test, or when the full backup is unavailable.

6.5 Manual Backup — Export to Desktop (Local)

Downloads a .json file directly to your computer. Use as a safety net before any restore operation.

1. Go to Admin → Data & Backup Management
2. Click ⬇ Export Backup
3. File downloads immediately as proposal-builder-backup-YYYY-MM-DD.json

6.6 Restore from S3

Use to recover from data loss or roll back to an earlier state.

1. First, run ⬇ Export Backup (desktop) to save a current snapshot as a safety net
2. Go to Admin → Data & Backup Management
3. Click ☁ Restore from S3
4. The modal lists all S3 backups, newest first, with date, size, and timestamp
5. Click the backup you want to restore from
6. Read the confirmation summary carefully — it shows proposal/contact/section counts
7. Click Restore to confirm
8. Wait for the log to complete (typically 10–30 seconds)
9. Verify: check Rate Card, SOW, Project Details, and a sample proposal

What Restore Does

6.7 Restore from File (Desktop)

1. Go to Admin → Data & Backup Management
2. Click ↑ Restore from File
3. Select your .json backup file
4. Review the confirmation summary and click Restore

Behavior is identical to Restore from S3 — same merge/replace logic.

6.8 Backup PDFs Only

1. Go to Admin → Data & Backup Management
2. Click 📑 Backup PDFs → S3
3. Wait for the toast — shows PDFs backed up and skipped (no archived PDF = skipped)

6.9 S3 Backup File Structure

The daily automated backup writes to a fixed, date-only key — a duplicate scheduler firing on the same day overwrites that one file rather than creating a new one. Manual runs (Run Full Backup Now, Export Data → S3) are timestamped instead, so multiple manual runs on the same day each keep their own file.

6.10 Backup Checklist — After Major Changes

Run after: bulk data import, schema change, recovery operation, or major catalog edit.

• Export to Desktop — download a local .json snapshot
• Export Data → S3 — upload an immediate cloud snapshot
• Verify the toast shows expected counts (proposals, contacts, sections)
• If PDFs were involved — run Backup PDFs → S3
• Optionally run a restore test on a non-production day

7. Environment Variables Reference

Netlify Environment Variables

Set in Netlify → Site configuration → Environment variables.

Client-Side (in db.js)

These are intentionally in client-side code — they are scoped to Row Level Security policies.

8. Access & User Groups

Manage access at Admin → Settings → User Groups. Changes take effect on the user's next login.

9. Deployment & Code Changes

1. Edit files locally in the catalyst folder
2. Test in the browser
3. Commit: git add <files> && git commit -m "Description"
4. Push: git push — Netlify auto-deploys within ~1 minute

Check Netlify → Deploys for deploy status. After any schema change in Supabase: update db.js, update scheduled-backup.js if new tables need backing up, update dbFullBackup() / dbFullRestore(), run a manual backup, commit and push.

10. Standing Up a New Environment

Use this section to deploy the Proposal Builder from scratch — migration, staging environment, or catastrophic recovery.

10.1 Prerequisites

10.2 Step 1 — Get the Source Code

# Option A — Clone existing repo
git clone https://github.com/inno-hlth/catalyst.git
cd catalyst

# Option B — New repo from downloaded ZIP
cd catalyst
git init
git add .
git commit -m "Initial commit"
git remote add origin https://github.com/YOUR-ORG/YOUR-REPO.git
git push -u origin main

10.3 Step 2 — Create Supabase Project & Run Schema SQL

1. Go to app.supabase.com → New project
2. Wait for provisioning, then go to Project Settings → API and note the Project URL, anon key, and service_role key
3. Go to SQL Editor and run the schema below

-- proposals
create table proposals (
  id uuid primary key default gen_random_uuid(),
  created_at timestamptz default now(), updated_at timestamptz default now(),
  client_name text, client_company text, status text default 'Draft',
  data jsonb, pdf_sent_url text, pdf_accepted_url text
);
alter table proposals enable row level security;
create policy "Auth users full access" on proposals for all using (auth.role() = 'authenticated');

-- settings
create table settings (key text primary key, value jsonb, updated_at timestamptz default now());
alter table settings enable row level security;
create policy "Auth users full access" on settings for all using (auth.role() = 'authenticated');

-- users (presenters)
create table users (
  id uuid primary key default gen_random_uuid(),
  name text, title text, company text, email text, sort_order integer default 0,
  created_at timestamptz default now()
);
alter table users enable row level security;
create policy "Auth users full access" on users for all using (auth.role() = 'authenticated');

-- contacts
create table contacts (
  id uuid primary key default gen_random_uuid(),
  first_name text, last_name text, email text, phone text, title text,
  company text, agency text, linkedin text, notes text,
  pub_cvb boolean default false, pub_rb boolean default false, pub_he boolean default false,
  tags text[] default '{}', updated_at timestamptz default now()
);
alter table contacts enable row level security;
create policy "Auth users full access" on contacts for all using (auth.role() = 'authenticated');

-- companies
create table companies (
  id uuid primary key default gen_random_uuid(),
  name text, website text, phone text, address text, city text,
  state text, zip text, industry text, notes text,
  updated_at timestamptz default now()
);
alter table companies enable row level security;
create policy "Auth users full access" on companies for all using (auth.role() = 'authenticated');

-- invoices
create table invoices (
  id uuid primary key default gen_random_uuid(),
  proposal_id uuid references proposals(id) on delete set null,
  company text, label text, amount numeric(10,2), status text default 'pending',
  paid boolean default false, paid_at timestamptz, cancelled_at timestamptz,
  created_at timestamptz default now(), updated_at timestamptz default now()
);
alter table invoices enable row level security;
create policy "Auth users full access" on invoices for all using (auth.role() = 'authenticated');

-- catalog_sections (one row per section per brand: cvb / rb / he)
create table catalog_sections (
  id uuid primary key default gen_random_uuid(),
  brand text not null, name text not null, sort_order integer default 0,
  hint text default '', badge text default '', updated_at timestamptz default now()
);
alter table catalog_sections enable row level security;
create policy "Auth users full access" on catalog_sections for all using (auth.role() = 'authenticated');

-- catalog_items
create table catalog_items (
  id uuid primary key default gen_random_uuid(),
  section_id uuid references catalog_sections(id) on delete cascade,
  brand text, name text, description text, price numeric(10,2) default 0,
  enabled boolean default true, type text default 'checkbox', sort_order integer default 0,
  suffix text default '', qty_label text default '', qty_max integer,
  multi_instance boolean default false, has_month_picker boolean default false,
  exclusive boolean default false, updated_at timestamptz default now()
);
alter table catalog_items enable row level security;
create policy "Auth users full access" on catalog_items for all using (auth.role() = 'authenticated');

-- sow_bullets
create table sow_bullets (
  id uuid primary key default gen_random_uuid(),
  section_id uuid references catalog_sections(id) on delete cascade,
  text text, bold boolean default false, sort_order integer default 0,
  updated_at timestamptz default now()
);
alter table sow_bullets enable row level security;
create policy "Auth users full access" on sow_bullets for all using (auth.role() = 'authenticated');

-- project_detail_bullets
create table project_detail_bullets (
  id uuid primary key default gen_random_uuid(),
  catalog_section_id uuid references catalog_sections(id) on delete cascade,
  text text, bold boolean default false, owner text default '',
  sort_order integer default 0, updated_at timestamptz default now()
);
alter table project_detail_bullets enable row level security;
create policy "Auth users full access" on project_detail_bullets for all using (auth.role() = 'authenticated');

-- ─────────────────────────────────────────────────────────────────────────────
-- Data API grants (required for new Supabase projects created after May 30 2026)
-- Without these, PostgREST / supabase-js returns a "42501" permission error.
-- ─────────────────────────────────────────────────────────────────────────────
grant select, insert, update, delete on public.proposals             to anon, authenticated, service_role;
grant select, insert, update, delete on public.settings              to anon, authenticated, service_role;
grant select, insert, update, delete on public.users                 to anon, authenticated, service_role;
grant select, insert, update, delete on public.contacts              to anon, authenticated, service_role;
grant select, insert, update, delete on public.companies             to anon, authenticated, service_role;
grant select, insert, update, delete on public.invoices              to anon, authenticated, service_role;
grant select, insert, update, delete on public.catalog_sections      to anon, authenticated, service_role;
grant select, insert, update, delete on public.catalog_items         to anon, authenticated, service_role;
grant select, insert, update, delete on public.sow_bullets           to anon, authenticated, service_role;
grant select, insert, update, delete on public.project_detail_bullets to anon, authenticated, service_role;

Authentication & Storage

• Authentication → Providers — ensure Email is enabled
• Authentication → Users → Invite user — invite each user by email
• Storage → New bucket — name: proposals, set to Public

10.4 Step 3 — Update db.js

// db.js lines 4-5
const SUPABASE_URL = 'https://YOUR-NEW-PROJECT.supabase.co';
const SUPABASE_KEY = 'YOUR-NEW-ANON-KEY';

git add db.js && git commit -m "Update Supabase credentials" && git push

10.5 Step 4 — Set Up Resend

1. Go to resend.com → Domains → Add Domain
2. Add DNS records (SPF, DKIM, DMARC) — wait for verification
3. Go to API Keys → Create API Key — copy the key (re_...)
4. If the domain changes, update the from: field in: send-proposal.js, send-accounting.js, send-invoices.js, scheduled-backup.js

10.6 Step 5 — Set Up AWS S3

1. S3 → Create bucket — block all public access ON (access is via signed URLs)
2. IAM → Users → Create user — attach AmazonS3FullAccess or a scoped bucket policy
3. Security credentials → Create access key — copy Access Key ID and Secret

10.7 Step 6 — Create & Configure Netlify Site

1. app.netlify.com → Add new site → Import an existing project
2. Connect GitHub → select repo. Build settings auto-detected from netlify.toml.
3. Click Deploy site
4. Go to Site configuration → Environment variables and add all variables:

After adding variables: Deploys → Trigger deploy to redeploy.

10.8 Step 7 — Seed Initial Data

Option A — Restore from backup (recommended for migrations): Log in as Admin → Admin → ↑ Restore from File — select your .json backup. This restores all proposals, contacts, catalog, and settings.

Option B — Fresh start: Log in as Admin → Admin → Database Migration → ⚡ Run Catalog Migration. Then manually configure pricing, SOW bullets, and Project Details.

10.9 Verification Checklist

Authentication

• Login page loads at the site URL
• Can log in with a test user account
• Redirected to Home after login

Rate Card & Catalog

• Admin → Rate Card shows all sections and items with prices
• Admin → SOW shows bullets
• Admin → Project Details shows bullets

Proposal Builder

• Can create a new proposal
• Rate Card items selectable, investment summary calculates correctly
• Can save a proposal — appears in Proposals list

Email

• Send Proposal — PDF email arrives at client address
• Send to Accounting — accounting email arrives

Backup

• Admin → ▶ Run Full Backup Now completes successfully
• Confirmation email received at BACKUP_NOTIFY_EMAIL
• Admin → ☁ Restore from S3 — backup appears in the list

Projects

• Projects board loads
• Deliverables panel shows bullets for accepted proposals

10.10 Migrating from the Existing Environment

1. Run a full backup on the old site — Admin → ▶ Run Full Backup Now
2. Export to desktop — Admin → ⬇ Export Backup — save a local .json
3. Stand up the new environment following Steps 1–7 above
4. Restore the backup on the new site — Admin → ↑ Restore from File
5. Re-invite all users via Supabase → Authentication → Users (auth users do not transfer between Supabase projects)
6. Verify the checklist in Step 8
7. Update DNS to point to the new Netlify site

Appendix — Quick Reference Card

Daily / Weekly Tasks

If Something Goes Wrong