Knowledge base index — Ringly#

Ringly sells a cloud phone system (calls + SMS) to support and sales teams. Customers are accounts; their staff who log in are users (seats); the people they call are contacts.

Sources#

• sources/ringly/ — PostgreSQL, schema public, 15 tables. See _source.md for dialect notes.

Where to look#

Also: glossary.md (business terms), rules.md (personal data, default filters), examples/.

Table map#

plans ← subscriptions → accounts ← users, teams, phone_numbers, contacts, calls, messages, events, integrations, support_tickets, nps_responses, invoices. team_members links teams and users. calls and messages reference phone_numbers, users (nullable) and contacts (nullable).

Glossary#

Customer / account#

A company paying for Ringly: a row in accounts. "Customers" means accounts unless the question is clearly about an account's own callers (then contacts).

Active customer#

accounts.status = 'active'. Trials (status = 'trial') are not customers yet.

Churned customer#

accounts.status = 'churned'; the churn date is accounts.churned_at.

MRR / ARR#

Monthly recurring revenue: sum of subscriptions.mrr_usd for status = 'active' subscriptions. ARR = MRR × 12. See metrics/mrr.md. Trialing subscriptions have MRR 0.

Seats#

subscriptions.seats (paid seats). Not the same as the number of rows in users, which can include invited and deactivated people.

Agent#

A user with role = 'agent'. "Staff" or "users" means all roles.

Answer rate, missed calls, wait time, talk time, AHT#

Call KPIs, defined in metrics/call-kpis.md. "Missed calls" in everyday language means inbound calls that were not answered: status missed, voicemail or abandoned.

NPS#

Net Promoter Score from nps_responses, see metrics/nps.md.

Region#

accounts.country is ISO alpha-2. "Europe" = every country except US. "DACH" = DE, AT, CH.

Rules#

Personal data#

• users.email, users.full_name, contacts.full_name, contacts.phone_e164 and messages.body are personal data. Do not select them unless the user explicitly asks for names or contact details of specific people. Use ids or account names in aggregates.
• Never select messages.body for analysis; count messages instead.

Default filters#

• Revenue and MRR use active subscriptions only (subscriptions.status = 'active').
• Call KPIs about "our customers' support quality" use inbound calls (direction = 'inbound').
• Exclude churned accounts from "current" questions; include them for historical ones.

Dates#

• All timestamps are timestamptz in UTC. "Last week" means the previous Monday–Sunday, "last month" the previous full calendar month. Use date_trunc.
• Money is USD.

Call KPIs#

All from calls. Unless the question says otherwise, use inbound calls only.

SELECT a.name AS account,
       COUNT(*) AS inbound_calls,
       COUNT(*) FILTER (WHERE c.status = 'answered') AS answered,
       ROUND(100.0 * COUNT(*) FILTER (WHERE c.status = 'answered') / COUNT(*), 1) AS answer_rate_pct,
       ROUND(AVG(c.wait_seconds)) AS avg_wait_s
FROM calls c
JOIN accounts a ON a.id = c.account_id
WHERE c.direction = 'inbound'
GROUP BY a.name
ORDER BY answer_rate_pct;

MRR and ARR#

MRR: sum of subscriptions.mrr_usd over subscriptions with status = 'active'. mrr_usd is already normalized to a month for annual plans and already multiplied by seats, so do not multiply by seats or plans.price_per_seat_usd again.

SELECT SUM(s.mrr_usd) AS mrr_usd, SUM(s.mrr_usd) * 12 AS arr_usd
FROM subscriptions s
WHERE s.status = 'active';

By plan:

SELECT p.name AS plan, p.billing_interval, COUNT(*) AS subscriptions, SUM(s.seats) AS seats,
       SUM(s.mrr_usd) AS mrr_usd
FROM subscriptions s
JOIN plans p ON p.id = s.plan_id
WHERE s.status = 'active'
GROUP BY p.name, p.billing_interval
ORDER BY mrr_usd DESC;

Churned MRR: mrr_usd of subscriptions with status = 'canceled', dated by ended_at.

NPS (Net Promoter Score)#

From nps_responses.score (0–10): promoters 9–10, passives 7–8, detractors 0–6. NPS = % promoters − % detractors, a number from −100 to 100 (not a percentage).

SELECT COUNT(*) AS responses,
       ROUND(100.0 * COUNT(*) FILTER (WHERE score >= 9) / COUNT(*)
           - 100.0 * COUNT(*) FILTER (WHERE score <= 6) / COUNT(*)) AS nps
FROM nps_responses;

question: Which customers have the worst answer rate on inbound calls?
query: |
  SELECT a.name AS account, COUNT(*) AS inbound_calls,
         ROUND(100.0 * COUNT(*) FILTER (WHERE c.status = 'answered') / COUNT(*), 1) AS answer_rate_pct
  FROM calls c JOIN accounts a ON a.id = c.account_id
  WHERE c.direction = 'inbound'
  GROUP BY a.name
  ORDER BY answer_rate_pct, inbound_calls DESC
verified_by: demo

question: What is our current MRR by plan?
query: |
  SELECT p.name AS plan, p.billing_interval, SUM(s.seats) AS seats, SUM(s.mrr_usd) AS mrr_usd
  FROM subscriptions s
  JOIN plans p ON p.id = s.plan_id
  WHERE s.status = 'active'
  GROUP BY p.name, p.billing_interval
  ORDER BY mrr_usd DESC
verified_by: demo

question: What is our NPS and how many promoters and detractors do we have?
query: |
  SELECT COUNT(*) AS responses,
         COUNT(*) FILTER (WHERE score >= 9) AS promoters,
         COUNT(*) FILTER (WHERE score BETWEEN 7 AND 8) AS passives,
         COUNT(*) FILTER (WHERE score <= 6) AS detractors,
         ROUND(100.0 * COUNT(*) FILTER (WHERE score >= 9) / COUNT(*)
             - 100.0 * COUNT(*) FILTER (WHERE score <= 6) / COUNT(*)) AS nps
  FROM nps_responses
verified_by: demo

question: Which open support tickets are high priority or urgent?
query: |
  SELECT t.id, a.name AS account, t.subject, t.category, t.priority, t.status, t.created_at
  FROM support_tickets t JOIN accounts a ON a.id = t.account_id
  WHERE t.status IN ('open', 'pending') AND t.priority IN ('high', 'urgent')
  ORDER BY CASE t.priority WHEN 'urgent' THEN 0 ELSE 1 END, t.created_at
verified_by: demo

Source: ringly#

• Engine: PostgreSQL 17, schema public. Read-only role fastdata_ro.
• Use Postgres syntax: date_trunc('month', ts), now() - interval '30 days', COUNT(*) FILTER (WHERE ...), ILIKE, properties->>'provider' for jsonb.
• Table and column comments are in the catalog: SELECT obj_description('calls'::regclass) / col_description('calls'::regclass, 6).
• Data is a small test fixture (6–7 rows per table), current up to late October 2026.

accounts#

Ringly customers (companies). Columns: id, name, industry, country (ISO alpha-2), size_band (1-10 | 11-50 | 51-200 | 201-1000 | 1000+), status (trial | active | churned), created_at (sign-up), churned_at (NULL unless churned). Referenced by almost every table through account_id.

calls#

One row per phone call. Columns: id, account_id → accounts, phone_number_id → phone_numbers, user_id → users (NULL when no agent answered), contact_id → contacts (nullable), direction (inbound | outbound), status (answered | missed | voicemail | abandoned), started_at, answered_at (NULL unless answered), ended_at, wait_seconds, talk_seconds (0 unless answered), cost_usd (Ringly's carrier cost). KPIs: metrics/call-kpis.md. Duration of a call = talk_seconds, not ended_at − started_at.

contacts#

The people an account calls or texts (its own customers). All personal data. Columns: id, account_id → accounts, full_name, phone_e164, company (nullable), created_at.

events#

Product analytics events. Columns: id, account_id, user_id (NULL for system events), event_name, occurred_at, properties (jsonb). Event names: login, call_started, integration_connected, trial_started, report_exported, voicemail_played. Read properties with properties->>'key', e.g. properties->>'device'.

integrations#

Third-party apps connected to an account. Columns: id, account_id, provider (salesforce | hubspot | zendesk | slack), status (connected | error | disconnected), connected_at, disconnected_at. error = still installed but failing to sync.

invoices#

Columns: id, account_id → accounts, subscription_id → subscriptions, period_start, period_end (dates), amount_usd, status (open | paid | overdue | void), issued_at, paid_at. Annual plans are invoiced once for the whole year, so invoice amounts are not MRR. "Outstanding" = status open or overdue.

messages#

SMS messages. Columns: id, account_id, phone_number_id, user_id (NULL for inbound), contact_id, direction (inbound | outbound), body (PII, never select for analysis), status (received for inbound; delivered | failed for outbound), sent_at. Delivery rate = delivered ÷ outbound.

nps_responses#

NPS survey answers. Columns: id, account_id, user_id, score (0–10), comment (nullable, free text), submitted_at. See metrics/nps.md.

phone_numbers#

Numbers rented by accounts. Columns: id, account_id → accounts, e164 (e.g. +12125550101), country, number_type (local | toll_free | mobile), status (active | released), created_at, released_at.

plans#

Price plans. price_per_seat_usd is per seat per month, also for annual plans. Columns: id, code (unique, e.g. pro_monthly), name (Starter | Professional | Enterprise | Basic), billing_interval (month | year), price_per_seat_usd, included_minutes (per seat per month), is_active (false = legacy plan still on old subscriptions).

subscriptions#

One row per subscription period of an account to a plan. Columns: id, account_id → accounts, plan_id → plans, seats, mrr_usd (monthly, already × seats), status (trialing | active | canceled), started_at, ended_at (NULL while running). Revenue questions: see metrics/mrr.md.

support_tickets#

Tickets accounts open with Ringly support. Columns: id, account_id, opened_by_user_id (nullable), subject, category (billing | call_quality | integration | onboarding | other), priority (low | normal | high | urgent), status (open | pending | solved), created_at, solved_at. "Open tickets" = status open or pending. Resolution time = solved_at − created_at.

team_members#

Many-to-many between teams and users. Columns: team_id → teams, user_id → users, joined_at. A user can be in several teams; count DISTINCT user_id when counting people.

teams#

Call queues / groups inside an account. Columns: id, account_id → accounts, name, created_at. Members are in team_members.

users#

People who log in to Ringly. Columns: id, account_id → accounts, email (PII), full_name (PII), role (admin | supervisor | agent), status (invited | active | deactivated), created_at, last_login_at (NULL = never logged in).