User Data Model — Fields, Validation, and Database Schema

The users table is the foundation of the Estructuras authentication system. Every person who registers on the platform receives a user record containing their contact details, a bcrypt-hashed password, and a timestamp tracking their most recent login. The UserModel class exposes three static async methods — find, register, and login — that handle all database interaction, including uniqueness enforcement and secure credential verification, so no controller ever touches raw SQL for user operations.

Database Fields

string (UUID)

required

A universally unique identifier generated at registration time via Node.js’s built-in randomUUID() from the node:crypto module. Serves as the primary key for the users table.

import { randomUUID } from "node:crypto";
const id = randomUUID(); // e.g. "a3f1c2d4-89ab-4cde-b012-1234567890ab"

name

string

required

The user’s full display name. Must not contain any numeric characters — enforced at the schema level via a regex refinement before the record ever reaches the database.

string

required

A valid email address in standard local@domain.tld format. Validated by Zod’s built-in .email() check. Must be unique across all user records; attempting to register with a duplicate email throws an error before any INSERT is attempted.

phone

string

required

A phone number matching the international-aware regex pattern:

(\+?\d{1,3}[-. ]?)?\(?\d{3}\)?[-. ]?\d{3}[-. ]?\d{4}

Accepts formats such as +1 (555) 123-4567, 555.123.4567, and 5551234567. Must be unique across all user records.

username

string

required

The user’s chosen login handle. Must be unique across all user records. Used as the lookup key during the login flow.

password_hash

string

required

The bcrypt hash of the user’s raw password, computed with SALT_ROUNDS from the application config. The plaintext password is never stored and is never returned in any API response.

A timestamp column set to CURRENT_TIMESTAMP when the user row is first inserted (i.e., at registration). Updated to the current date/time on every successful call to UserModel.login().

Validation Rules

All incoming user data is validated through a Zod schema defined in src/schemas/user.schema.js before being passed to any model method. Validation failures return a structured error object rather than throwing, letting controllers return descriptive 400 responses.

Show Full registration schema (validateUser)

// src/schemas/user.schema.js
import { z } from "zod";

const phoneRegex = new RegExp(
  /^(\+?\d{1,3}[-. ]?)?\(?\d{3}\)?[-. ]?\d{3}[-. ]?\d{4}$/
);
const containsNumberRegex = new RegExp(/[0-9]/);

const userSchema = z.object({
  name: z
    .string({
      invalid_type_error: "El nombre debe ser texto.",
      required_error: "EL nombre es requerido.",
    })
    .refine(
      (value) => !containsNumberRegex.test(value),
      "El nombre no debe contener números"
    ),
  email: z
    .string({ required_error: "EL email es requerido." })
    .email("Formato de email invalido"),
  phone: z
    .string({ required_error: "EL teléfono es requerido." })
    .regex(phoneRegex, "Formato de teléfono inválido"),
  username: z.string({
    invalid_type_error: "El username debe ser texto",
    required_error: "El username es requerido",
  }),
  password: z.string({
    invalid_type_error: "La contraseña debe ser texto",
    required_error: "La contraseña es requerida",
  }),
});

export function validateUser(input) {
  return userSchema.safeParse(input);
}

Per-field constraints

Field	Rule	Error message (es)
`name`	Must be a string; must not match `/[0-9]/`	`"El nombre no debe contener números"`
`email`	Must be a string; must pass Zod’s `.email()` format check	`"Formato de email invalido"`
`phone`	Must be a string; must match the international phone regex	`"Formato de teléfono inválido"`
`username`	Must be a string	`"El username es requerido"`
`password`	Must be a string	`"La contraseña es requerida"`

validatePartialUser wraps the same schema with .partial(), making every field optional. In practice this is used for the login endpoint, which only supplies username and password.

export function validatePartialUser(input) {
  return userSchema.partial().safeParse(input);
}

Model Methods

All methods are static async members of the UserModel class exported from src/models/auth.model.js. The database client is the Turso libSQL client imported from src/db.js.

`UserModel.find(id)`

Looks up a single user row by primary key.

const user = await UserModel.find("a3f1c2d4-89ab-4cde-b012-1234567890ab");

Show Implementation

static find = async (id) => {
  const user = await db.execute(`SELECT * FROM users WHERE id = ?`, [id]);
  return user.rows[0]; // undefined if no match
};

Parameter	Type	Description
`id`	string	UUID of the user to look up

Returns: the matching user row object, or undefined if no record exists for that id.

`UserModel.register(data)`

Creates a new user account. Performs a pre-insert uniqueness check on username, email, and phone simultaneously, hashes the password with bcrypt, then inserts the full row.

const newUser = await UserModel.register({
  name: "María García",
  email: "maria@example.com",
  phone: "+1 (555) 987-6543",
  username: "mariagarcia",
  password: "s3cr3tP@ssword",
});

Show Implementation

static register = async (data) => {
  const { name, email, phone, username, password } = data;
  const id = randomUUID();
  const hashedPassword = await bcrypt.hash(password, SALT_ROUNDS);

  const user = await db.execute(
    `SELECT * FROM users WHERE username = ? OR email = ? OR phone = ?`,
    [username, email, phone]
  );

  if (user.rows.length > 0) {
    throw new Error("Username, email o teléfono ya registrados.");
  }

  await db.execute(
    `INSERT INTO users (id, name, email, phone, username, password_hash, last_login)
     VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)`,
    [id, name, email, phone, username, hashedPassword]
  );

  return { id, ...data };
};

Parameter	Type	Description
`data.name`	string	Full display name (no digits allowed)
`data.email`	string	Valid, unique email address
`data.phone`	string	Valid, unique phone number
`data.username`	string	Unique login handle
`data.password`	string	Plaintext password — hashed before save

Returns: a new user object { id, name, email, phone, username, password }.
Throws: "Username, email o teléfono ya registrados." if any uniqueness check fails.

The returned object still contains the original password field from the spread of data. The password_hash stored in the database is not included. Controllers should strip the password field before sending the response to clients.

Authenticates a user by username and password. Verifies the bcrypt hash, updates last_login, and returns the user’s public profile fields.

const session = await UserModel.login({
  username: "mariagarcia",
  password: "s3cr3tP@ssword",
});

Show Implementation

static login = async (data) => {
  const { username, password } = data;
  const last_login = new Date();

  const user = await db.execute(
    `SELECT * FROM users WHERE username = ?`,
    [username]
  );

  if (user.rows.length === 0) {
    throw new Error("Usuario no encontrado");
  }

  const { id, name, email, phone } = user.rows[0];

  const isPasswordValid = await bcrypt.compare(
    password,
    user.rows[0].password_hash
  );

  if (!isPasswordValid) {
    throw new Error("Usuario o Contraseña incorrecta");
  }

  await db.execute({
    sql: `UPDATE users SET last_login = ? WHERE id = ?`,
    args: [id, last_login], // ⚠ bug in source: SQL is SET last_login = ? WHERE id = ? but args are [id, last_login] — order is reversed
  });

  return { id, name, email, phone, username, last_login };
};

Parameter	Type	Description
`data.username`	string	The user’s unique login handle
`data.password`	string	The user’s plaintext password

Returns: { id, name, email, phone, username, last_login } — note that password_hash is intentionally excluded.
Throws: "Usuario no encontrado" if the username does not exist.
Throws: "Usuario o Contraseña incorrecta" if the password does not match the stored hash.
Throws: "Error al actualizar el usuario" if the last_login UPDATE fails.

Known bug — last_login UPDATE args order is reversed. The SQL statement is UPDATE users SET last_login = ? WHERE id = ?, which expects [last_login, id], but the source passes args: [id, last_login]. As a result, the last_login column receives the user’s id string instead of the current timestamp, and the WHERE clause receives the timestamp instead of the UUID — causing the UPDATE to match no rows and silently leave last_login unchanged.

Security Notes

Passwords are never stored in plaintext. The register method hashes every password with bcrypt.hash(password, SALT_ROUNDS) before any database write. The raw password exists in memory only for the duration of the hash computation.

password_hash is never returned to API consumers. The login method destructs only { id, name, email, phone } from the database row before building its return value, ensuring the hash is never forwarded downstream. Build your session tokens or cookies from the returned object alone.

The SALT_ROUNDS constant is imported from src/config.js and controls the bcrypt work factor. A higher value increases hash time and resistance to brute-force attacks at the cost of slightly slower registration and login responses.

Users

Properties

Data Models

User Data Model — Fields, Validation, and Database Schema

Database Fields

Validation Rules

Per-field constraints

Model Methods

`UserModel.find(id)`

`UserModel.register(data)`

Security Notes

Build docs developers (and LLMs) love

Users

Properties

Data Models

Documentation Index

​Database Fields

​Validation Rules

​Per-field constraints

​Partial validation (login)

​Model Methods

​UserModel.find(id)

​UserModel.register(data)

​UserModel.login(data)

​Security Notes

Build docs developers (and LLMs) love

Database Fields

Validation Rules

Per-field constraints

Partial validation (login)

Model Methods

`UserModel.find(id)`

`UserModel.register(data)`

`UserModel.login(data)`

Security Notes