Appropriate data types for each column (no stringly-typed data)

Monetary values stored as FLOAT lose cents through floating-point rounding — a $29.99 price becomes $29.989999... in arithmetic, producing billing discrepancies. Timestamps stored as VARCHAR cannot be range-queried or timezone-converted by the database. Enums stored as unbounded TEXT accept any string value, so typos like 'actve' or 'CANCELED' silently enter the database and bypass all application-level validation. These are not hypothetical: AI-generated schemas routinely default to TEXT for everything and FLOAT for any numeric value. ISO 25010 functional-correctness penalizes these because the database cannot enforce domain constraints it was not given.

Fix type mismatches one column at a time using migration ALTER COLUMN ... TYPE. Monetary columns must use DECIMAL/NUMERIC or integer cents. Enum columns must use a Postgres ENUM type or a CHECK constraint.

-- Monetary: DECIMAL not FLOAT
ALTER TABLE orders
  ALTER COLUMN total TYPE DECIMAL(12, 2);

-- Enum: add CHECK constraint
ALTER TABLE subscriptions
  ADD CONSTRAINT chk_status
  CHECK (status IN ('active', 'canceled', 'past_due', 'trialing'));

-- Timestamp: convert VARCHAR to TIMESTAMPTZ
ALTER TABLE users
  ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at::TIMESTAMPTZ;

model Order {
  total  Decimal @db.Decimal(12, 2)  // not Float
  status OrderStatus                 // enum, not String
}
enum OrderStatus { active canceled past_due trialing }

• ID: database-design-operations.schema-design.appropriate-types

• Severity: medium

• What to look for: List all columns in the schema and for each, classify the data type as "correct" or "mismatched." Common violations in AI-generated schemas: (1) Timestamps stored as TEXT/VARCHAR instead of TIMESTAMP WITH TIME ZONE or DateTime. (2) Booleans stored as INTEGER (0/1) or VARCHAR ('true'/'false') instead of BOOLEAN. (3) Enums stored as unbounded TEXT/VARCHAR with no check constraint or enum type, leaving the column open to any string value. (4) UUIDs stored as VARCHAR(36) or TEXT instead of UUID type (Postgres). (5) Monetary values stored as FLOAT/DOUBLE PRECISION — floating point arithmetic loses cents, use DECIMAL/NUMERIC(10,2) or store as integer cents. (6) IP addresses stored as VARCHAR instead of INET (Postgres). (7) JSON data stored as TEXT instead of JSONB/JSON.

• Pass criteria: Column types match their semantic meaning — no more than 2 type mismatches allowed. Timestamps use TIMESTAMP/DateTime/TIMESTAMPTZ. Booleans use BOOLEAN. Monetary values use DECIMAL/NUMERIC (not FLOAT). UUIDs use UUID type in Postgres. Enums use ENUM type, Postgres enum, or a CHECK constraint to restrict values. JSON blobs use JSONB/JSON.

• Fail criteria: More than 2 columns use incorrect types. Money stored as FLOAT. Timestamps stored as strings. Booleans stored as integers. Enums as unbounded text.

• Skip (N/A) when: N/A — always applicable for any project with a database.

• Detail on fail: Specify each type mismatch. Example: "'orders.total' stored as FLOAT — floating point imprecision causes rounding errors for monetary values." or "'users.created_at' is VARCHAR(255) — should be TIMESTAMPTZ." or "'subscriptions.status' is TEXT with no constraint — accepts any string value; should be an enum or have a CHECK constraint.".

• Remediation: Use appropriate types:

  -- Monetary values — use DECIMAL, not FLOAT
  ALTER TABLE orders
    ALTER COLUMN total TYPE DECIMAL(12, 2);
  -- Or store as integer cents (no decimal storage needed):
  -- total_cents BIGINT NOT NULL  (e.g., 2999 = $29.99)
  
  -- Timestamps — use TIMESTAMPTZ
  ALTER TABLE users
    ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at::TIMESTAMPTZ;
  
  -- Booleans — use BOOLEAN
  ALTER TABLE users
    ALTER COLUMN is_active TYPE BOOLEAN USING (is_active::INTEGER)::BOOLEAN;
  
  -- Enums — add CHECK constraint or use enum type
  ALTER TABLE subscriptions
    ADD CONSTRAINT chk_status CHECK (status IN ('active', 'canceled', 'past_due', 'trialing'));
  -- Or create a proper enum type:
  CREATE TYPE subscription_status AS ENUM ('active', 'canceled', 'past_due', 'trialing');
  ALTER TABLE subscriptions ALTER COLUMN status TYPE subscription_status USING status::subscription_status;
  

  // Prisma — correct types
  model Order {
    total     Decimal   @db.Decimal(12, 2)  // not Float
    createdAt DateTime  @default(now())      // not String
    isActive  Boolean   @default(true)       // not Int
    status    OrderStatus                    // enum, not String
  }
  
  enum OrderStatus {
    active
    canceled
    past_due
    trialing
  }