Database type best practices

Select appropriate data types and defaults for database columns to ensure data integrity and simplify application code. For timestamp columns, use timezone-aware types (TIMESTAMPTZ) when working across different timezones or with UTC offsets. For ID columns, configure auto-generation at the database level using functions like gen_random_uuid() rather than relying on application code to generate identifiers.

CREATE TABLE "Entity" (
  -- Auto-generated UUID for primary key
  "id" TEXT NOT NULL DEFAULT gen_random_uuid(),
  -- Timezone-aware timestamps for accurate time handling
  "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ(3) NOT NULL,
  -- Other columns
  "name" TEXT NOT NULL,
  -- JSON for flexible structured data
  "metadata" JSONB
);

This approach reduces bugs related to timezone confusion, simplifies code by delegating ID generation to the database, and ensures consistent handling of data types across the application.