← nyxcore-systems
7 min read

Shipping Smarter Reports: Persistence, PDFs, and the Path to Download Nirvana

A deep dive into a recent dev session, covering the journey of implementing robust report persistence, dynamic PDF exports, and user-friendly download controls across our project dashboard.

TypeScriptPrismaNext.jsPDFMarkdownPythonPlaywrightPostgreSQLdevelopment workflowfrontendbackendreport generationtechnical debt

Alright team, another session wrapped, another chunk of the roadmap solidified. This was a continuation, picking up context from where we last left off, and I'm happy to report: mission accomplished. We've officially shipped report persistence, integrated a slick PDF export mechanism, and rolled out download controls across the project dashboard. It's all tidied up in commit d123ccf on main, and yes, TypeScript is squeaky clean.

Let's break down the journey, the wins, and the hard-won lessons.

The Core Mission: Beyond Ephemeral Reports

Our primary goal for this session was ambitious: transform our dynamic, on-the-fly reports into persistent, shareable assets. This meant:

  1. Report Persistence: Saving generated reports to the database.
  2. PDF Export: Converting Markdown reports into beautifully formatted PDFs.
  3. Download Controls: Giving users the power to download their reports in both Markdown and PDF formats directly from the dashboard.

Diving into the "Done" List: From Database to UI

This wasn't a small task, touching nearly every layer of our stack.

1. Backend Foundations: Persistence & Schema

The first step was giving our reports a home.

  • Prisma Model & RLS: We introduced a new Report Prisma model in prisma/schema.prisma. Crucially, we also implemented Row Level Security (RLS) in prisma/rls.sql to ensure that users can only access reports associated with projects they have permissions for.
  • tRPC Router: A dedicated src/server/trpc/routers/reports.ts was created to handle the CRUD operations (list, get, delete) for these new persistent reports.
  • Mutation Updates: The existing generateReport mutations (for autoFix, refactor, and workflows) were updated to now persist the generated report data to the database after creation. This was a critical piece, ensuring every report generated is now automatically saved.

2. The PDF Engine: Python & Playwright to the Rescue

Converting Markdown to a robust, branded PDF required a dedicated service.

  • md2pdf.py: Our new scripts/md2pdf.py Python script is the heart of our PDF generation. It leverages md2pdf-mermaid (which in turn uses Playwright and Chromium) to render Markdown, including Mermaid diagrams, into a PDF. We even added a custom branded footer – a nice touch for professionalism.
  • Next.js API Endpoint: To expose this Python script to our frontend, we created src/app/api/v1/reports/pdf/route.ts. This endpoint acts as a proxy, receiving report content, invoking the Python script, and streaming the generated PDF back to the client.

3. Frontend Polish: UI, Downloads & User Experience

With the backend and PDF engine in place, the focus shifted to the user experience.

  • ReportsTab Overhaul: The ReportsTab in src/app/(dashboard)/dashboard/projects/[id]/page.tsx got a significant rewrite. It now elegantly displays saved reports, grouped by type, and features a dedicated viewer Sheet panel for each. There's also a clear section for generating new reports.
  • ReportGeneratorModal Enhancements: This modal was updated with new utility functions (buildFilename(), downloadFile(), downloadPdf()) and now proudly sports dual Markdown and PDF download buttons. We also ensured the projectName prop was correctly passed down, allowing for more context-aware report generation.
  • Download Buttons & Loading States: We added dedicated Markdown and PDF download buttons to the saved report viewer Sheet panel in ReportsTab (around lines 2760-2830 of the project page). To provide a smooth UX, pdfLoading state was introduced, complete with a spinner for the PDF button. Icon imports (Download, FileDown) were also added for visual clarity.
  • Housekeeping: A small but important fix involved correcting an import path in src/app/api/v1/reports/pdf/route.ts from ../../../middleware to ../../middleware. Also, .venv/ was added to .gitignore to keep our repository clean. Finally, a full npm run typecheck confirmed our TypeScript was happy.

Hard-Won Lessons: Navigating the Trenches

No development session is complete without its share of head-scratching moments. Here’s what we learned:

Lesson 1: Prisma's Unsupported Types & Raw SQL Lifelines

  • The Problem: We're using PostgreSQL's vector(1536) type for embeddings in our workflow_insights table. While incredibly powerful, Prisma currently marks this as an Unsupported type. This became a critical issue whenever we ran prisma db push --accept-data-loss for schema changes. Prisma, in its attempt to reconcile the schema, would drop this embedding column every single time.
  • The Takeaway: ORMs are fantastic, but they don't always support every bleeding-edge or niche database feature. When you encounter Unsupported types, be prepared to manage them outside the ORM's full control. Sometimes, dropping to raw SQL is the most pragmatic solution.
  • The Workaround: Our solution involves a post-db:push manual step. After every schema migration, we restore the column and its HNSW index using raw SQL:
    sql
    ALTER TABLE workflow_insights ADD COLUMN IF NOT EXISTS embedding vector(1536);
-- And then recreate the HNSW index if it was dropped
    This is a recurring maintenance point, and it's vital to remember this step until Prisma offers native support or a more elegant workaround.

Lesson 2: The Perils of Relative Paths in Next.js API Routes

  • The Problem: While setting up src/app/api/v1/reports/pdf/route.ts, I initially used ../../../middleware for an import. This resulted in a TS2307: Cannot find module error.
  • The Takeaway: Relative paths can be tricky, especially when dealing with nested API routes or moving files around within a framework like Next.js. It's easy to miscount the ../ segments, leading to frustrating import errors. A quick mental map or ls -F in the terminal can save a lot of time.
  • The Fix: The middleware was actually located at src/app/api/v1/middleware.ts, meaning the correct path from src/app/api/v1/reports/pdf/route.ts was ../../middleware. A simple fix, but a common pitfall.

Current State & Key Dependencies

A few notes on the current environment:

  • Python Venv: Our PDF generation relies on a Python virtual environment (.venv/) containing md2pdf-mermaid (v1.4.3), Playwright, and Chromium.
  • Hardcoded Path: The src/app/api/v1/reports/pdf/route.ts currently hardcodes the Python executable path to .venv/bin/python3 on line 11. This is something to keep in mind for future deployments or different environments.
  • .gitignore: .venv/ is now correctly ignored, but new clones will need to run:
    bash
    python3 -m venv .venv && \
.venv/bin/pip install md2pdf-mermaid && \
.venv/bin/playwright install chromium
  • Database: The reports table is active in PostgreSQL with RLS enabled. All repositories are linked to projects via projectId FK (established in an earlier session via manual SQL).

Looking Ahead: What's Next?

With robust report generation and persistence now live, our immediate focus shifts to:

  1. Context-Aware AutoFix & Refactor Pipelines: The next major push involves enhancing our AutoFix and Refactor pipelines with richer context. This will involve further schema changes, building a pipeline context loader, and integrating a frontend project selector (deep-wiggling-aho.md).
  2. Expanding PDF Downloads: Currently, PDF downloads are available in the ReportsTab and ReportGeneratorModal. We should consider adding this functionality to the standalone auto-fix, refactor, and workflow detail pages for consistency.
  3. Streamlining Setup: To improve the onboarding experience for new developers, we need to create a dedicated setup script or clear documentation for the Python venv and Playwright dependency.

That's it for this session. A solid chunk of work, bringing significant value to our reporting capabilities. Onwards!

json
{
  "thingsDone": [
    "Implemented Report Prisma model and RLS",
    "Created tRPC router for report management",
    "Updated generateReport mutations to persist reports",
    "Developed Python md2pdf.py script for PDF export with branded footer",
    "Created Next.js API endpoint for PDF generation",
    "Rewrote ReportsTab for saved reports and viewer Sheet",
    "Enhanced ReportGeneratorModal with download functions and buttons",
    "Added Markdown and PDF download buttons to ReportsTab",
    "Implemented pdfLoading state and spinners for PDF downloads",
    "Corrected module import path in PDF API route",
    "Added .venv/ to .gitignore",
    "Ensured TypeScript type-checking is clean"
  ],
  "pains": [
    "Prisma's lack of native support for PostgreSQL `vector(1536)` leading to column drops on `db push`",
    "Incorrect relative import path for middleware in a new Next.js API route"
  ],
  "successes": [
    "Full implementation of report persistence from generation to database storage",
    "Successful integration of external Python script for robust PDF generation (including Mermaid diagrams)",
    "Seamless user experience for downloading reports in multiple formats",
    "Clean TypeScript codebase after significant feature additions",
    "Established a clear workaround for Prisma's `Unsupported` type issue"
  ],
  "techStack": [
    "Next.js",
    "TypeScript",
    "Prisma",
    "PostgreSQL",
    "tRPC",
    "Python",
    "md2pdf-mermaid",
    "Playwright",
    "Chromium",
    "Markdown"
  ]
}