<aside> ⚙️
Canonical reference for the FSP Marketing Dashboard Funnel. Every per-tab metrics page cites this page for the shared data sources, base filters, product scopes, timezone handling, and caching rules. All rules below are verified against source; each is cited as path:line.
</aside>
The app spans three PostgreSQL connections, wired in config/database.yml. Every ActiveRecord model inherits from the base class for its connection.
DATABASE_URL (config/database.yml:55-58). App-owned tables: users, forecasts, access_grants, ad-platform spend tables. Standard ApplicationRecord.fsp_sql — the fsp-bi-hub warehouse (config/database.yml:62-70), reached via Rails.application.credentials.dig(:database, ...). Holds the synced HubSpot tables (hubspot_contacts, hubspot_deals, hubspot_form_submissions), Sunrise projects, and the ServiceTitan / NetSuite feeds. Models inherit from FspSqlBase, which does establish_connection :fsp_sql (app/models/fsp_sql_base.rb).fsp_api_hub — read-only fsp-api-hub Postgres (config/database.yml:74-83), credentials api_hub_database. Holds the raw leads table (3PL vendor acquisition cost) + a settings CAC mapping. Models inherit from FspApiHubBase (app/models/fsp_api_hub_base.rb); e.g. Lead (app/models/lead.rb).<aside> 💡
In test, both fsp_sql and fsp_api_hub are pointed at the local fsp_marketing_dash_test database (see the Rails.env.test? ternaries in config/database.yml). Dev and prod both connect to the same AWS RDS instance.
</aside>
hubspot_contacts.hs_object_id (bigint) — PK.hubspot_deals.hs_object_id (bigint) — PK; hubspot_deals.contact_id (bigint) → hubspot_contacts.hs_object_id (FK, added by the loader).hubspot_form_submissions.hs_object_id (bigint) — PK; contact_id → hubspot_contacts.hs_object_id.Canonical deal→contact join:
SELECT ...
FROM hubspot_deals
INNER JOIN hubspot_contacts
ON hubspot_deals.contact_id = hubspot_contacts.hs_object_id
The "New Contacts" universe. Composed scope HubspotContact.new_contacts_base (app/models/hubspot_contact.rb:23-32), chaining the eight predicate scopes at app/models/hubspot_contact.rb:11-20.
lead_type = 'Residential'
AND lead_source IS NOT NULL
AND (NOT ost OR ost IS NULL)
AND aged_lead IS NOT TRUE
AND (NOT return_accepted OR return_accepted IS NULL)
AND phone IS NOT NULL
AND (NOT is_battery_auto_quote OR is_battery_auto_quote IS NULL)
AND hs_analytics_source_data_1 NOT IN ('CONVERSATIONS', 'EXTENSION')
<aside> 💡
Contacted stage uses a LOOSER base. contacted_leads_base (app/models/hubspot_contact.rb:39-46) drops the phone IS NOT NULL and hs_analytics_source_data_1 NOT IN (...) predicates — it keeps only residential + with_lead_source + non_ost + non_aged + non_return + non_auto_quote. This mirrors f2p contacted_leads.py. Do not assume New Leads and Contacted share the same denominator.
</aside>
The CLAUDE.md "New Contacts base filter" list matches the code exactly. Its note that contacted is looser is captured by the separate contacted_leads_base scope.