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

- **Pattern:** `ab-000901` (`database-design-operations.schema-design.appropriate-types`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000901
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000901)

## Why it matters

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.

## Severity rationale

Medium because type mismatches cause data corruption and silent validation failures, but are typically contained to specific columns rather than system-wide integrity failures.

## Remediation

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.

```sql
-- 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;
```

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

## Detection

- **ID:** `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:

  ```sql
  -- 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
  // 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
  }
  ```

---

## External references

- iso-25010 functional-suitability.functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000901