Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• cooltools/shared/schema.ts
• cooltools/server/storage.ts
• cooltools/drizzle.config.ts
• cooltools/package.json
• cooltools/lib/phoneApi.ts

Introduction

The database schema for the CoolTools phone comparison platform is designed to store comprehensive mobile device specifications, user-generated content, and historical tracking data. It utilizes a relational structure to manage relationships between core phone entities and auxiliary data such as benchmark scores, price history, and user reviews.

The system is built on PostgreSQL and managed via the Drizzle ORM, providing a type-safe interface for database operations. The schema supports complex querying requirements for advanced searching and side-by-side comparisons of up to four devices.
Sources: cooltools/shared/schema.ts:1-100, cooltools/server/storage.ts:1-10

Architecture and Tools

The database layer is integrated into a Next.js environment with the following technology stack:

• Database Engine: PostgreSQL.
• ORM: Drizzle ORM for schema definition and type safety.
• Migration Tool: drizzle-kit for pushing schema changes.
• Connection Pooling: Managed via pg (node-postgres) with a maximum of 20 connections.

Sources: cooltools/package.json:11-18, cooltools/drizzle.config.ts:1-10, cooltools/server/storage.ts:8-15

Connection Management

The application uses a managed pool to interact with the database, handling environment-specific SSL requirements and graceful shutdowns.
Sources: cooltools/server/storage.ts:8-35

The diagram shows the connection flow from the application layer to the PostgreSQL database through the connection pool.
Sources: cooltools/server/storage.ts:8-15

Data Models

Core Entities: Phones

The phones table serves as the primary repository for device information. It contains flattened columns for frequently accessed specifications (display, performance, battery) and a jsonb field for exhaustive technical data.
Sources: cooltools/shared/schema.ts:4-68

Field	Type	Description
id	serial	Primary Key
brand	text	Manufacturer name
model	text	Model name
slug	text	Unique URL-friendly identifier
display_size	real	Screen size in inches
antutu_score	integer	Performance benchmark
full_specs	jsonb	Complete technical data object
Sources: cooltools/shared/schema.ts:5-68

Supporting Entities

The schema includes several tables linked to the primary phones table via foreign keys to provide specialized functionality.

• comparisons: Stores user-saved comparison sets using a jsonb array of phone IDs.
• price_history: Tracks fluctuations in phone pricing across different retailers.
• reviews: Contains user ratings, textual content, and a structured jsonb object for pros and cons.
• benchmarks: Stores various benchmark types (AnTuTu, Geekbench, 3DMark) and their specific details.

Sources: cooltools/shared/schema.ts:71-109

Entity Relationship Diagram

The diagram illustrates the one-to-many relationships between the central phone entity and its related metrics and user interactions.
Sources: cooltools/shared/schema.ts:4-109

External Data Integration

While the database stores persistent local data, the application frequently interfaces with the api-mobilespecs.azharimm.dev service. The local PhoneSpec interface used in the API layer mirrors the structure of the jsonb data stored in the database's full_specs column.
Sources: cooltools/lib/phoneApi.ts:4-68

Configuration

The database configuration is managed via drizzle.config.ts, specifying the schema location and PostgreSQL dialect.
Sources: cooltools/drizzle.config.ts:1-10

// cooltools/drizzle.config.ts:4-10
export default {
  schema: './shared/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
}

Conclusion

The Database Schema provides a robust foundation for the CoolTools platform, balancing structured relational data for core specifications with flexible JSON storage for exhaustive technical details. This hybrid approach allows the system to perform efficient searches on primary attributes while maintaining the depth required for high-fidelity phone comparisons.
Sources: cooltools/shared/schema.ts:4-109, cooltools/server/storage.ts:1-35