Shipping Robust Reports: Persistence, PDF Export, and Overcoming Prisma's Vector Challenge

Every now and then, a development session clicks into place, delivering a significant chunk of functionality that feels incredibly satisfying. Today, I want to share the journey of a recent sprint where we brought robust report persistence, dynamic PDF export, and a much-improved user experience to our project dashboard.

The goal was clear: empower users to generate, save, and download detailed project reports, complete with Mermaid diagrams, in both Markdown and PDF formats. This wasn't just about adding a button; it was about building a reliable backend, integrating a Python-based PDF engine, and ensuring a seamless frontend experience.

The Mission Accomplished: A Feature-Packed Delivery

After a focused session, all the pieces fell into place. The core achievement was the full implementation of report persistence, PDF export via md2pdf-mermaid, and intuitive download controls across our project dashboard. This entire phase was wrapped up and pushed to main under commit d123ccf.

Here's a breakdown of what we shipped:

1. Robust Report Persistence

The foundation for our new reporting capabilities involved making reports first-class citizens in our data model.

Database Schema: We introduced a new Report Prisma model in prisma/schema.prisma, complete with Row Level Security (RLS) in prisma/rls.sql to ensure data privacy and access control.
API Endpoints: A dedicated src/server/trpc/routers/reports.ts was crafted to handle report listing, retrieval, and deletion.
Integration: Our existing generateReport mutations (for autoFix, refactor, and workflows) were updated to automatically persist newly generated reports, ensuring they're saved for future reference.

2. Dynamic PDF Export with Markdown & Mermaid Support

This was one of the more exciting parts – bringing beautifully formatted PDFs to life.

Python Powerhouse: We developed scripts/md2pdf.py, a Python script leveraging md2pdf-mermaid and Playwright (with a Chromium browser) to convert Markdown content (including Mermaid diagrams!) into branded PDFs.
API Gateway: A new API endpoint, src/app/api/v1/reports/pdf/route.ts, was created to serve as the bridge between our Next.js backend and the Python PDF converter. This endpoint handles the request, calls the Python script, and streams the generated PDF back to the client.

3. Enhanced Frontend & User Experience

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

Report Generator Modal: The ReportGeneratorModal was supercharged with new functions like buildFilename(), downloadFile(), and downloadPdf(), alongside dual Markdown and PDF download buttons.
Revamped Reports Tab: The ReportsTab on the project detail page underwent a significant rewrite. It now elegantly displays saved reports grouped by type, offers a dedicated viewer Sheet for detailed inspection, and includes a clear section for generating new reports. Crucially, we added Markdown and PDF download buttons directly into the saved report viewer.
Loading States & Icons: To provide visual feedback, pdfLoading state was added to the ReportsTab for the PDF button, showing a spinner during generation. We also imported necessary icons (Download, FileDown) for a polished look.
Contextual Data: Ensured the projectName prop was correctly passed through ReportsTab to ReportGeneratorModal, making filenames and reports more context-aware.

4. Housekeeping & Polish

Import Path Fix: A small but critical fix in src/app/api/v1/reports/pdf/route.ts corrected an incorrect import path (../../../middleware to ../../middleware), resolving a TS2307 error.
Environment Management: Added .venv/ to .gitignore to keep our repository clean.
Type Safety: Ran npm run typecheck to confirm everything remained TypeScript clean – always a good feeling!

Challenges & Lessons Learned

No significant feature delivery is without its bumps. These moments are invaluable for learning and improving our development process.

The Persistent Prisma `vector(1536)` Conundrum

Problem: Our workflow_insights table uses a vector(1536) column for embeddings, which Prisma currently marks as an Unsupported type. This means that every time we tried to apply schema changes using prisma db push --accept-data-loss, Prisma would drop this crucial column. Data loss and broken embeddings were the immediate consequences.

Solution/Workaround: Since Prisma doesn't yet natively support this type for migrations, we've adopted a disciplined manual workaround. After every prisma db push, we execute a raw SQL command to restore the column and its associated HNSW index:

sql

ALTER TABLE workflow_insights ADD COLUMN IF NOT EXISTS embedding vector(1536);
-- And then re-create any necessary indices, e.g.,
-- CREATE INDEX workflow_insights_embedding_hnsw ON workflow_insights USING hnsw (embedding vector_l2_ops);

Lesson: While we await better Prisma support for vector types, understanding the underlying database schema and having a quick, reliable restore mechanism is critical. This highlights the importance of thorough pre- and post-migration checks, especially when dealing with advanced or unsupported data types.

The Elusive Relative Import Path

Problem: During the setup of our new PDF API endpoint (src/app/api/v1/reports/pdf/route.ts), I initially made a common mistake: an incorrect relative import path for our middleware. Trying ../../../middleware resulted in a TS2307: Cannot find module error.

Solution: A quick correction to ../../middleware resolved the issue. The middleware was located at src/app/api/v1/middleware.ts, meaning the PDF route only needed to go up two levels from src/app/api/v1/reports/pdf/route.ts to src/app/api/v1/ before descending into middleware.ts.

Lesson: Relative import paths can be deceptively tricky, especially in nested directory structures. Double-checking the actual file system path against the import statement is a fundamental debugging step that often gets overlooked in the heat of development.

Under the Hood: Key Technical Details

Python Virtual Environment: Our PDF generation relies on a Python virtual environment (.venv/) containing md2pdf-mermaid (version 1.4.3), Playwright, and Chromium.
Hardcoded Path: The src/app/api/v1/reports/pdf/route.ts currently hardcodes the path to the Python executable as .venv/bin/python3. This works for our current setup but is a candidate for improvement (e.g., via environment variables) for more flexible deployments.

Dependency Setup: For any new clone of the repository, the Python environment needs to be set up:

bash

python3 -m venv .venv
.venv/bin/pip install md2pdf-mermaid playwright
.venv/bin/playwright install chromium

PostgreSQL reports Table: The reports table is now live in PostgreSQL, leveraging Row Level Security for secure data handling.
Project-Report Linkage: All reports are correctly linked to their respective projects via a projectId foreign key, established through manual SQL in a prior session.

What's Next?

With report persistence and PDF export firmly in place, our immediate focus shifts to:

Context-Aware AutoFix & Refactor Pipelines: The next major step involves implementing more intelligent context loading for our auto-fix and refactor pipelines, starting with schema changes and a frontend project selector.
PDF Downloads Everywhere: While the main ReportsTab and ReportGeneratorModal now have PDF download, we'll consider adding this functionality to standalone auto-fix, refactor, and workflow detail pages for broader accessibility.
Streamlined Setup: Creating a setup script or comprehensive documentation for the Python virtual environment and Playwright dependencies will greatly improve the onboarding experience for new developers.

This session was a fantastic example of integrating multiple technologies – TypeScript, Next.js, Prisma, Python, and Playwright – to deliver a valuable feature. Tackling the challenges head-on only made the final delivery more rewarding. Stay tuned for more updates as we continue to build!