Reports Schema

This page documents how reports are stored and rendered in CRMBuilder. An AI agent building any RP-prefixed template must read this before writing any code.

How reports work

Reports are not stored as table rows — they are server-rendered Next.js pages that query CRM tables directly. There is no reports table. Each report is a page at /reports/[slug] that:

1. Authenticates via getServerSession
2. Scopes all queries to id_company from the session
3. Runs one or more SQL queries against CRM tables
4. Renders the results as a table, chart, or stat cards
5. Optionally exports as CSV

File structure for a new report

src/app/(crm)/reports/
  page.tsx              ← Report index (list of available reports)
  [slug]/
    page.tsx            ← Individual report page

Follow the module scaffolding pattern in core/custom-modules. Reports live under src/app/(crm)/ and inherit the CRM layout.

Nav registration

Reports are listed on /reports (the index page). Add a new report to the reports index page component — do not add individual reports to the main nav.

Standard report page template

import { getServerSession } from 'next-auth';
import { authOptions } from '@/lib/auth';
import { redirect } from 'next/navigation';
import { db } from '@/lib/db';

export default async function YourReportPage() {
  const session = await getServerSession(authOptions);
  if (!session) redirect('/login');

  const { id_company } = session.user;

  const rows = await db.query(`
    SELECT ...
    FROM your_tables
    WHERE id_company = ?
      AND deleted_at IS NULL
    ORDER BY created_at DESC
  `, [id_company]);

  return <YourReportComponent rows={rows} />;
}

CSV export pattern

Every report page should include a CSV export button. The pattern:

// API route: /api/reports/[slug]/export
export async function GET(req: Request) {
  const session = await getServerSession(authOptions);
  if (!session) return new Response('Unauthorized', { status: 401 });

  const { id_company } = session.user;

  const rows = await db.query(`SELECT ...`, [id_company]);

  const csv = [
    Object.keys(rows[0]).join(','),           // header row
    ...rows.map(r => Object.values(r).join(','))
  ].join('\n');

  return new Response(csv, {
    headers: {
      'Content-Type': 'text/csv',
      'Content-Disposition': 'attachment; filename="report.csv"'
    }
  });
}

Key tables for common report types

Pipeline / deals reports

SELECT d.title, d.value, d.status,
       ds.name AS stage_name,
       c.first_name, c.last_name
FROM deals d
JOIN deal_stages ds ON ds.id = d.id_stage
JOIN customers c ON c.id = d.id_customer
WHERE d.id_company = :id_company
  AND d.deleted_at IS NULL
  AND d.status = 'open'
ORDER BY d.value DESC;

SMS campaign performance

SELECT
  ca.name AS campaign_name,
  COUNT(ci.id) AS total_sent,
  COUNT(ci.replied_at) AS total_replied,
  ROUND(COUNT(ci.replied_at)::numeric /
    NULLIF(COUNT(ci.id), 0) * 100, 1) AS reply_rate
FROM campaigns ca
JOIN campaigns_items ci ON ci.id_campaign = ca.id
WHERE ca.id_company = :id_company
  AND ca.deleted_at IS NULL
  AND ci.deleted_at IS NULL
GROUP BY ca.id, ca.name
ORDER BY reply_rate DESC;

Team activity

SELECT
  u.name AS team_member,
  COUNT(CASE WHEN d.status = 'won' THEN 1 END) AS deals_won,
  COUNT(d.id) AS total_deals,
  SUM(CASE WHEN d.status = 'won' THEN d.value ELSE 0 END) AS revenue
FROM pb_users u
LEFT JOIN deals d ON d.id_owner = u.id
  AND d.id_company = :id_company
  AND d.deleted_at IS NULL
WHERE u.id_company = :id_company
GROUP BY u.id, u.name
ORDER BY revenue DESC;

What does not exist

• No reports table — do not attempt to INSERT report definitions
• No deal_stage_history table — time-in-stage must be approximated using deals.updated_at (not accurate for multi-stage movement)
• No contact_activities table — activity is tracked in activity_log and smslog/emaillog separately
• No report scheduling system — scheduled email digests would require a new cron job, not a report page