Optimize vault aggregations using advanced PostgreSQL CTEs

[Fatimasanusi]

Description

Calculating total volume, unique holders, and price histories using sequential Prisma queries leads to the N+1 problem and high API latency.

Architecture & Context

To serve the complex frontend dashboards, the database layer must leverage PostgreSQL Common Table Expressions (CTEs). A single, highly optimized raw SQL query should calculate the aggregate metrics for the fractional vaults, bypassing Prisma's object-mapping overhead for reads.

Technical Requirements

• Write raw SQL CTEs to aggregate share volume and historical pricing.
• Execute the query using Prisma's $queryRaw method.
• Create a materialized view for data older than 24 hours.

Acceptance Criteria

• Dashboard aggregation query executes in under 50ms.
• N+1 query patterns are completely eliminated from the endpoint.
• Materialized views refresh seamlessly via a background cron job.