D1 Database Schema

Neemias' database is an SQLite managed by Cloudflare D1. Migrations are applied sequentially in the migrations/ folder. Below is the complete schema after all 19 migrations.

v0.56.0: The users.role column was migrated to a user_roles join table (migration 0019).

Conventions

• UUIDs are stored as TEXT PRIMARY KEY (generated via crypto.randomUUID())
• Timestamps use ISO 8601 format (TEXT)
• Booleans are INTEGER (0 = false, 1 = true)
• JSON is stored as TEXT (serialized)
• Soft-delete uses status column (ACTIVE | DELETED)

---

Tables

users

System users with PBKDF2 authentication.

Column	Type	Constraints
user_id	TEXT	PRIMARY KEY
email	TEXT	UNIQUE NOT NULL
display_name	TEXT	NOT NULL
status	TEXT	NOT NULL, DEFAULT 'ACTIVE'
password_hash	TEXT
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

v0.56.0: role column removed. Roles are now stored in user_roles join table (see below).

user_roles

User-role join table for multi-role support (v0.56.0+).

Column	Type	Constraints
user_id	TEXT	NOT NULL, REFERENCES users(user_id) ON DELETE CASCADE
role	TEXT	NOT NULL
created_at	TEXT	NOT NULL

Primary key: composite (user_id, role).

students

Registered students with complete registration data.

Column	Type	Constraints
student_id	TEXT	PRIMARY KEY
display_name	TEXT	NOT NULL
photo_ref	TEXT
status	TEXT	NOT NULL, DEFAULT 'ACTIVE'
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL
deleted_justification	TEXT
guardian_name	TEXT	NOT NULL, DEFAULT ''
guardian_name_alt	TEXT
birth_date	TEXT	YYYY-MM-DD format
phones	TEXT	NOT NULL, DEFAULT '[]' (JSON array)
address_street	TEXT
address_number	TEXT
address_complement	TEXT
address_neighborhood	TEXT
address_city	TEXT
address_state	TEXT
address_zip	TEXT
class_id	TEXT	REFERENCES classes(class_id)
nucleus_participates	INTEGER	NOT NULL, DEFAULT 0
nucleus_region	TEXT
nucleus_name	TEXT
allergies	TEXT
special_needs	TEXT
family_membership_status	TEXT	NOT NULL, DEFAULT 'DESCONHECIDO'

Indexes:

• idx_students_status_updated_at — (status, updated_at DESC)

family_membership_status values: MEMBRO, NAO_MEMBRO, DESCONHECIDO

classes

Classes by age range, with seed of 7 default classes.

Column	Type	Constraints
class_id	TEXT	PRIMARY KEY
name	TEXT	NOT NULL UNIQUE
age_min	INTEGER
age_max	INTEGER
status	TEXT	NOT NULL, DEFAULT 'ACTIVE'
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

Indexes:

• idx_classes_status — (status, name)

Seed: Nursery (1-2), Toddler (3), Kindergarten (4-5), Children (6), Juniors 1 (7), Juniors 2 (8), Pre-Teens (9-11)

nuclei

Nuclei (cells) organized by region.

Column	Type	Constraints
nucleus_id	TEXT	PRIMARY KEY
region	TEXT	NOT NULL
name	TEXT	NOT NULL
status	TEXT	NOT NULL, DEFAULT 'ACTIVE'
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

Indexes:

• idx_nuclei_region — (region, status)

Fixed regions: Sky Blue, Blue, Yellow, White, Red, Orange, Green

attendance_events

Immutable attendance events (event sourcing). Each call generates an event.

Column	Type	Constraints
event_id	TEXT	PRIMARY KEY
student_id	TEXT	NOT NULL, REFERENCES students(student_id)
action_type	TEXT	NOT NULL (MARK_PRESENT or MARK_ABSENT)
happened_at	TEXT	NOT NULL
actor_user_id	TEXT	NOT NULL
actor_role	TEXT	NOT NULL
server_timestamp	TEXT	NOT NULL
is_conflict_loser	INTEGER	NOT NULL, DEFAULT 0
created_at	TEXT	NOT NULL

Indexes:

• idx_attendance_events_student_id — (student_id, server_timestamp DESC)

student_events

Immutable student mutation events (CREATE, UPDATE, DELETE).

Column	Type	Constraints
event_id	TEXT	PRIMARY KEY
student_id	TEXT	NOT NULL, REFERENCES students(student_id)
event_type	TEXT	NOT NULL
actor_user_id	TEXT	NOT NULL
actor_role	TEXT	NOT NULL
payload	TEXT	NOT NULL (JSON)
server_timestamp	TEXT	NOT NULL
is_conflict_loser	INTEGER	NOT NULL, DEFAULT 0
created_at	TEXT	NOT NULL

Indexes:

• idx_student_events_student_id — (student_id, server_timestamp DESC)

user_events

Immutable user mutation events.

Column	Type	Constraints
event_id	TEXT	PRIMARY KEY
target_user_id	TEXT	NOT NULL, REFERENCES users(user_id)
event_type	TEXT	NOT NULL
actor_user_id	TEXT	NOT NULL
actor_role	TEXT	NOT NULL
payload	TEXT	NOT NULL (JSON)
server_timestamp	TEXT	NOT NULL
created_at	TEXT	NOT NULL

auth_refresh_sessions

Refresh token sessions with rotation and compromise detection.

Column	Type	Constraints
session_id	TEXT	PRIMARY KEY
user_id	TEXT	NOT NULL, REFERENCES users(user_id) ON DELETE CASCADE
refresh_token_hash	TEXT	NOT NULL UNIQUE
csrf_token	TEXT	NOT NULL
expires_at	TEXT	NOT NULL
revoked_at	TEXT
replaced_by	TEXT	REFERENCES auth_refresh_sessions(session_id)
rotated_at	TEXT
suspected_compromise_at	TEXT
created_at	TEXT	NOT NULL

Indexes:

• idx_auth_refresh_sessions_user_id — (user_id, created_at DESC)
• idx_auth_refresh_sessions_expires_at — (expires_at)

idempotency_ledger

Idempotency record for detecting mutation replay.

Column	Type	Constraints
key	TEXT	PRIMARY KEY (composite with user_id)
user_id	TEXT	PRIMARY KEY (composite with key)
payload_hash	TEXT	NOT NULL
status_code	INTEGER	NOT NULL
response_body	TEXT	NOT NULL (JSON)
created_at	TEXT	NOT NULL

Indexes:

• idx_idempotency_ledger_created_at — (created_at DESC)

audit_logs

Audit trail for LGPD compliance.

Column	Type	Constraints
audit_id	TEXT	PRIMARY KEY
correlation_id	TEXT	NOT NULL
actor_user_id	TEXT	NOT NULL
actor_role	TEXT	NOT NULL
endpoint	TEXT	NOT NULL
outcome	TEXT	NOT NULL
details	TEXT	NOT NULL (JSON)
created_at	TEXT	NOT NULL

roles

Role catalog with granular permissions.

Column	Type	Constraints
name	TEXT	PRIMARY KEY
display_name	TEXT	NOT NULL
permissions	TEXT	NOT NULL, DEFAULT '[]' (JSON array)
is_system	INTEGER	NOT NULL, DEFAULT 0
created_at	TEXT	NOT NULL
updated_at	TEXT	NOT NULL

Seed (8+ system roles, is_system=1):

Role	Permissions
ADMIN	attendance, reports, students.*, users.*, settings, import-export, sessions.*, nuclei.manage, classes.manage
CHAMADOR	attendance, students.search
RELATORIOS	reports, students.search
CADASTRO	students.add, students.search, sessions.add, sessions.edit, nuclei.manage, classes.manage

System roles also include VOLUNTARIO, COORDENACAO_KIDS, ADMINISTRATIVO_KIDS, RESPONSAVEL. Full list in packages/permissions/index.ts.

rate_limits

D1-based rate limiting control.

Column	Type	Constraints
key	TEXT	PRIMARY KEY
count	INTEGER	NOT NULL, DEFAULT 1
window_start	INTEGER	NOT NULL
expires_at	INTEGER	NOT NULL

---

Migration History

Migration	File	Description
0001	0001_init.sql	Core schema: users, students, attendance_events, student_events, user_events, idempotency_ledger, audit_logs + indexes
0002	0002_auth_sessions.sql	auth_refresh_sessions table with rotation and revocation
0003	0003_student_fields.sql	Student expansion (guardian, phones, address) + new classes and nuclei tables + class seed
0004	0004_family_membership.sql	family_membership_status column in students
0005	0005_compromise_detection.sql	suspected_compromise_at column in auth_refresh_sessions
0006	0006_roles.sql	roles table + seed of the 4 system roles
0007	0007_rate_limits.sql	rate_limits table for rate control
0008	0008_sociodemographic.sql	Sociodemographic fields for students (9 pastoral fields)
0009	0009_atomic_token_rotation.sql	Atomic refresh token rotation with compromise detection
0010	0010_events.sql	Event registration tables (capacity, slots, registrations)
0011	0011_class_slots_sessions.sql	Class slots and sessions (weekly + one-off)
0012	0012_onboarding.sql	Parent onboarding approval queue + registration links
0013	0013_username.sql	Student username for OTP-based authentication
0014	0014_guardians.sql	Guardian relationship tracking & approval workflow
0015	0015_events.sql	Event registration refinements (naming fix)
0016	0016_notifications.sql	Notification infrastructure (SSE feed, push tokens)
0017	0017_roles_seed.sql	Seed additional system roles (VOLUNTARIO, RESPONSAVEL, etc.)
0018	0018_capacity.sql + 0018_merge_voluntario.sql	Class capacity limits + VOLUNTEER/VOLUNTARIO_KIDS merge
0019	0019_user_roles.sql	user_roles join table. DROP COLUMN users.role. Migrates existing roles. Multi-role support.

Total: 19 migrations (0001–0019). The 0018_merge_voluntario.sql is a companion migration alongside 0018_capacity.sql.

Source: migrations/