​

Database Schema

​

Entity Relationship Diagram

​

Table Specifications

​

Cars Table

CREATE TABLE cars (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    month TEXT,
    make TEXT,
    importer_type TEXT,
    fuel_type TEXT,
    vehicle_type TEXT,
    number INTEGER
);

• id: UUID primary key (auto-generated)
• month: Registration month in YYYY-MM format (e.g., “2024-01”)
• make: Vehicle manufacturer name (e.g., “TOYOTA”, “BMW”)
• importer_type: Registration type classification
• fuel_type: Fuel type (“Petrol”, “Diesel”, “Electric”, “Hybrid”)
• vehicle_type: Vehicle category (“Cars”, “Motor cycles”, “Buses”, etc.)
• number: Number of registrations for the specific combination

-- Optimized for common query patterns
CREATE INDEX month_make_idx ON cars (month, make);
CREATE INDEX month_idx ON cars (month);
CREATE INDEX make_idx ON cars (make);
CREATE INDEX fuel_type_idx ON cars (fuel_type);
CREATE INDEX make_fuel_type_idx ON cars (make, fuel_type);
CREATE INDEX number_idx ON cars (number);

• Monthly registration summaries by make
• Fuel type distribution analysis
• Vehicle type breakdowns
• Historical trend analysis

​

COE Bidding Results Table

CREATE TABLE coe (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    month TEXT,
    bidding_no INTEGER,
    vehicle_class TEXT,
    quota INTEGER,
    bids_success INTEGER,
    bids_received INTEGER,
    premium INTEGER
);

• id: UUID primary key
• month: Bidding month (YYYY-MM format)
• bidding_no: Bidding exercise number (1 or 2 per month)
• vehicle_class: COE category classification
• quota: Number of certificates available
• bids_success: Number of successful bids
• bids_received: Total number of bids submitted
• premium: Winning premium amount in SGD

-- Optimized for bidding analysis and historical queries
CREATE INDEX month_vehicle_idx ON coe (month, vehicle_class);
CREATE INDEX vehicle_class_idx ON coe (vehicle_class);
CREATE INDEX month_bidding_no_idx ON coe (month, bidding_no);
CREATE INDEX premium_idx ON coe (premium);
CREATE INDEX bids_idx ON coe (bids_success, bids_received);
CREATE INDEX month_bidding_no_vehicle_class_idx ON coe (month DESC, bidding_no DESC, vehicle_class);

• Latest bidding results by category
• Premium trend analysis
• Bidding success rate calculations
• Historical premium comparisons

​

COE Prevailing Quota Premium Table

CREATE TABLE coe_pqp (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    month TEXT,
    vehicle_class TEXT,
    pqp INTEGER
);

• id: UUID primary key
• month: Month in YYYY-MM format
• vehicle_class: COE category
• pqp: Prevailing Quota Premium rate in SGD

-- Optimized for PQP rate lookups
CREATE INDEX pqp_month_vehicle_class_idx ON coe_pqp (month, vehicle_class);
CREATE INDEX pqp_vehicle_class_idx ON coe_pqp (vehicle_class);
CREATE INDEX pqp_idx ON coe_pqp (pqp);

​

Blog Posts Table

CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    content TEXT NOT NULL,
    metadata JSONB,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW() NOT NULL,
    modified_at TIMESTAMP DEFAULT NOW() NOT NULL
);

• id: UUID primary key
• title: Blog post title
• slug: URL-friendly identifier (unique)
• content: Markdown-formatted blog content
• metadata: JSON blob containing tags, reading time, data source info, etc.
• published_at: Publication timestamp (NULL for drafts)
• created_at: Creation timestamp
• modified_at: Last modification timestamp

{
  "tags": ["cars", "market-analysis", "2024-01"],
  "reading_time": 5,
  "llm_model": "gemini-1.5-pro",
  "data_month": "2024-01",
  "data_type": "cars",
  "excerpt": "Analysis of January 2024 vehicle registrations...",
  "view_count": 0
}

• Published posts ordered by date
• Posts filtered by tags
• Posts by data month or type
• Blog post analytics

​

Analytics Table

CREATE TABLE analytics (
    id SERIAL PRIMARY KEY,
    date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    pathname TEXT NOT NULL,
    referrer TEXT,
    country TEXT,
    flag TEXT,
    city TEXT,
    latitude TEXT,
    longitude TEXT
);

• id: Auto-incrementing primary key
• date: Event timestamp with timezone
• pathname: Page URL path being accessed
• referrer: Referring URL (if available)
• country: Visitor country (from geolocation)
• flag: Country flag emoji
• city: Visitor city
• latitude: Geographic latitude
• longitude: Geographic longitude

• Page view counts by pathname
• Geographic visitor distribution
• Referrer analysis
• Traffic trends over time

​

Indexing Strategy

​

Performance Optimization

• (month, make): Cars filtered by month and manufacturer
• (month, vehicle_class): COE data by month and category
• (month, bidding_no, vehicle_class): Complex COE queries

• Date/month columns for temporal queries
• Category columns for grouping operations
• Numeric columns for range queries and sorting

​

Index Maintenance

​

Data Relationships

​

Conceptual Relationships

• posts.metadata.data_month relates to cars.month or coe.month
• posts.metadata.data_type indicates source table (“cars” or “coe”)

• Both tables share month and vehicle_class fields
• PQP rates complement bidding results for complete COE picture

​

Data Integrity

​

Migration Management

​

Drizzle ORM Integration

# 1. Modify schema files in packages/database/src/schema/
# 2. Generate migration
pnpm generate

# 3. Review generated SQL in migrations/ directory
# 4. Apply migration
pnpm migrate

# 5. Verify schema consistency
pnpm migrate:check

​

Schema Evolution

​

Performance Considerations

​

Query Optimization

​

Storage Efficiency

​

Scaling Considerations

​

Related Documentation

• System Architecture Overview
• Data Processing Workflows
• API Architecture
• Database Package Documentation