paste PostgreSQL or JSON, get your schema reviewed like a senior would

Boolean flags: BOOLEAN, not TINYINT, CHAR or INT

Postgres has a real BOOLEAN type, one byte, accepts true/false, and yet schemas keep growing flags stored as something else: active INT, fl_enabled CHAR(1) with 'S' and 'N', is_deleted SMALLINT. Every one of them is the same bug waiting: a type that can hold more than two values will, eventually, hold a third.

The tri-state problem

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    active INT NOT NULL DEFAULT 1
);

-- two years later
SELECT DISTINCT active FROM users;  -- 0, 1, 2, -1

Who wrote the 2? Nobody remembers; some batch job "temporarily" meant "suspended". Now every consumer needs to know that active = 2 exists, the WHERE active = 1 filters silently exclude rows they shouldn't, and the cleanup requires archaeology. A BOOLEAN column makes the invalid state unrepresentable, which is the whole point of types. If the domain genuinely has three states, that's not a flag, it's a status, and it wants an enum-shaped column with named values, not a magic number.

The char variants ('S'/'N', 'Y'/'N', 'T'/'F') add a second failure mode: case. 's', 'S' and ' S' are three different values to the database and one value to every human, and the CHECK constraint that was supposed to police it rarely exists.

Where the numeric flags come from: MySQL

MySQL has no real boolean: BOOLEAN is an alias for TINYINT(1), so every schema born there carries numeric flags by necessity, and the convention travels when the schema does. In a migration to Postgres, TINYINT(1) should become BOOLEAN, and the defaults must convert with it (DEFAULT 1 -> DEFAULT TRUE), that's exactly what Schemint's MySQL to PostgreSQL converter does with them. Converting an already-populated Postgres column later:

ALTER TABLE users
  ALTER COLUMN active TYPE BOOLEAN
  USING active <> 0,
  ALTER COLUMN active SET DEFAULT TRUE;

Nullable flags are a decision, usually the wrong one

A nullable BOOLEAN has three states: true, false, unknown. Occasionally that's the model (a consent the user hasn't answered yet), and then NULL legitimately means "unanswered". But when a flag has a sensible starting state, declare it:

active BOOLEAN NOT NULL DEFAULT true

Otherwise every reader handles the null branch forever, and half of them choose a different default. The tell in code review: WHERE active IS NOT FALSE, someone routing around a null they didn't want to exist.

How flags map to code

A NOT NULL BOOLEAN maps to a Java primitive-friendly Boolean with an initializer from the DEFAULT, to bool in Pydantic, to boolean in TypeScript. A nullable flag maps to Boolean that can be null, bool | None, boolean | null, and now the tri-state lives in every model, which is why it should be deliberate. A numeric flag maps to Integer/number, and the "is 2 truthy?" question follows your data across every language boundary it crosses.

Naming still matters: is_deleted, has_children, active read as predicates and generate clean accessors. A column named flag or status_flag with a boolean type is the reverse problem, a type that says yes/no and a name that refuses to say to what.

Schemint flags columns whose name says boolean but whose type says number when you paste your DDL, the cheapest moment to fix a flag is before the third value shows up.

Related