Generate a Complete User Table Schema from JSON

Building a Production User Table

A user table is the foundation of almost every application database. Converting a JSON user object into a well-designed SQL schema requires attention to data types, constraints, indexes, and security considerations.

Example JSON

{
  "id": "usr_7f3b2a1e",
  "email": "alice@example.com",
  "username": "alice_dev",
  "display_name": "Alice Johnson",
  "password_hash": "$2b$12$LJ3m4ys...",
  "avatar_url": "https://cdn.example.com/avatars/alice.jpg",
  "role": "admin",
  "is_active": true,
  "is_verified": true,
  "last_login_at": "2024-06-14T22:15:00Z",
  "created_at": "2024-01-10T08:00:00Z",
  "updated_at": "2024-06-14T22:15:00Z"
}

Generated SQL

CREATE TABLE users (
  id VARCHAR(20) PRIMARY KEY,
  email VARCHAR(320) NOT NULL,
  username VARCHAR(50) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  avatar_url VARCHAR(2048) NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'viewer',
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  last_login_at TIMESTAMP WITH TIME ZONE NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_users_email UNIQUE (email),
  CONSTRAINT uq_users_username UNIQUE (username),
  CONSTRAINT chk_users_role CHECK (role IN ('admin', 'editor', 'viewer'))
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_role ON users (role);
CREATE INDEX idx_users_created_at ON users (created_at);

Key Design Decisions

Email field — VARCHAR(320) per RFC 5321 maximum. Always UNIQUE and indexed since login queries hit this column.

Password hash — VARCHAR(255) stores bcrypt/argon2 output. Never store plain text passwords. The column name password_hash signals intent.

String ID — The prefixed string ID (usr_7f3b2a1e) is a common pattern in modern APIs. It provides type safety (you cannot accidentally pass an order ID where a user ID is expected).

Separate is_active and is_verified — Two boolean flags are clearer than a single status field. Active means the account is not suspended; verified means the email has been confirmed.

Timestamps — created_at and updated_at with timezone and defaults. last_login_at is nullable because a newly created user may not have logged in yet.

Security Considerations

• The password_hash column should never appear in SELECT queries by default. Consider creating a view that excludes it.
• Add rate-limiting at the application layer for login attempts.
• Consider storing email in lowercase and adding a functional index: CREATE UNIQUE INDEX ON users (LOWER(email)).

Soft Delete Extension

For user accounts, soft deletion is usually preferred:

deleted_at TIMESTAMP WITH TIME ZONE NULL

Add a partial unique index to allow email reuse after deletion: WHERE deleted_at IS NULL.