Data Dictionary and Event Schema

Neemias

1. Purpose

This document defines the canonical data contract for storage, event history, and synchronization payloads. It is the source of truth for entity fields, types, constraints, keys, and validation rules. The backend (Cloudflare D1) is the primary store; the local IndexedDB is an offline fallback.

2. Design Rules

• All timestamps stored in UTC ISO 8601 format.
• Brazilian Portuguese is default locale for rendering, not storage.
• Audit events are immutable.
• Student deletion is soft delete only and requires justification.
• Admin precedence applies when conflicts involve Admin actions.
• Non-admin conflicts use last write wins by server timestamp.

3. Enumerations

3.1 Roles

• ROLL_CALLER
• REPORT_USER
• DATA_ENTRY
• ADMIN_USER

3.2 Sync State

• PENDING
• IN_PROGRESS
• SYNCED
• FAILED
• RETRYING

3.3 Attendance Action

• MARK_PRESENT
• MARK_ABSENT

3.4 Student Lifecycle State

• ACTIVE
• DELETED

3.5 Event Type

• CREATE
• UPDATE
• DELETE
• MARK_PRESENT
• MARK_ABSENT

3.6 User Event Type

• USER_CREATED
• USER_UPDATED
• USER_DEACTIVATED
• USER_PASSWORD_RESET

4. Entity Dictionary

4.1 Student

Field	Type	Required	Key	Constraints
studentId	UUID	Yes	PK	Immutable
displayName	string	Yes		1 to 100 chars
photoRef	string	Yes		Local blob key or remote reference
status	enum	Yes		ACTIVE or DELETED
createdAt	datetime UTC	Yes		Immutable
updatedAt	datetime UTC	Yes		Updated on non-delete changes
createdBy	UUID	Yes	FK User.userId	Must exist
deletedAt	datetime UTC	No		Required when status is DELETED
deletedBy	UUID	No	FK User.userId	Required when status is DELETED

4.2 AttendanceEvent

Field	Type	Required	Key	Constraints
eventId	UUID	Yes	PK	Immutable
studentId	UUID	Yes	FK Student.studentId	Must exist
actionType	enum	Yes		MARK_PRESENT or MARK_ABSENT
actorId	UUID	Yes	FK User.userId	Must exist
actorRole	enum	Yes		Must match role at action time
timestamp	datetime UTC	Yes		Client event time
serverTimestamp	datetime UTC	No		Set when synced
syncState	enum	Yes		PENDING, IN_PROGRESS, SYNCED, FAILED, RETRYING
isConflictLoser	boolean	Yes		Default false
conflictSupersededBy	UUID	No	FK AttendanceEvent.eventId	Set when superseded

4.3 StudentEvent

Field	Type	Required	Key	Constraints
eventId	UUID	Yes	PK	Immutable
studentId	UUID	Yes	FK Student.studentId	Must exist
eventType	enum	Yes		CREATE, UPDATE, DELETE
actorId	UUID	Yes	FK User.userId	Must exist
actorRole	enum	Yes		Must match role at action time
timestamp	datetime UTC	Yes		Client event time
serverTimestamp	datetime UTC	No		Set when synced
changePayload	JSON	Yes		Before and after delta
justification	string	No		Required for DELETE, 10 to 500 chars
syncState	enum	Yes		PENDING, IN_PROGRESS, SYNCED, FAILED, RETRYING
isConflictLoser	boolean	Yes		Default false

4.4 Session

Field	Type	Required	Key	Constraints
sessionId	UUID	Yes	PK	Immutable
userId	UUID	Yes	FK User.userId	Must exist
role	enum	Yes		Snapshot at login
issuedAt	datetime UTC	Yes		Immutable
expiresAt	datetime UTC	Yes		issuedAt plus 24h policy
state	enum	Yes		ACTIVE, EXPIRED, REVOKED
lastValidatedAt	datetime UTC	No		Updated on auth checks
encryptedSessionSecrets	JSON	No		AES-GCM envelope for refresh token and CSRF token; plaintext fields should be empty when encrypted

4.5 SyncQueueEntry

Field	Type	Required	Key	Constraints
queueId	UUID	Yes	PK	Immutable
eventId	UUID	Yes	FK AttendanceEvent.eventId or StudentEvent.eventId	Must exist
entityType	string	Yes		ATTENDANCE_EVENT or STUDENT_EVENT
actionType	enum	Yes		Must match event payload
actorId	UUID	Yes	FK User.userId	Must exist
actorRole	enum	Yes		Snapshot at queue time
enqueuedAt	datetime UTC	Yes		Immutable
syncState	enum	Yes		PENDING, IN_PROGRESS, SYNCED, FAILED, RETRYING
retryCount	integer	Yes		0 to 5
failureCode	string	No		Last sync error code
failureReason	string	No		Last sync error detail
lastAttemptAt	datetime UTC	No		Updated on each attempt

4.6 User

Field	Type	Required	Key	Constraints
userId	UUID	Yes	PK	Immutable
username	string	Yes	UQ	3 to 50 chars, lowercase alphanumeric and hyphen
displayName	string	Yes		1 to 100 chars
passwordHash	string	Yes		SHA-256 hash only, plaintext forbidden
encryptedCredentials	JSON	No		AES-GCM envelope for credential material at rest
cryptoKeyVersion	integer	No		Per-user encryption key version
keyRotatedAt	datetime UTC	No		Last key rotation time
role	enum	Yes		ROLL_CALLER, REPORT_USER, DATA_ENTRY, ADMIN_USER
status	enum	Yes		ACTIVE or DEACTIVATED
createdAt	datetime UTC	Yes		Immutable
createdBy	UUID | null	Yes	FK User.userId	Null allowed for initial seed only
updatedAt	datetime UTC	Yes		Updated on non-deactivation changes
deactivatedAt	datetime UTC	No		Required when status is DEACTIVATED
deactivatedBy	UUID	No	FK User.userId	Required when status is DEACTIVATED

4.7 UserEvent

Field	Type	Required	Key	Constraints
eventId	UUID	Yes	PK	Immutable
userId	UUID	Yes	FK User.userId	Must exist
eventType	enum	Yes		USER_CREATED, USER_UPDATED, USER_DEACTIVATED, USER_PASSWORD_RESET
actorId	UUID	Yes	FK User.userId	Must exist
actorRole	enum	Yes		Must match role at action time
timestamp	datetime UTC	Yes		Client event time
changePayload	JSON	Yes		Never include passwordHash
encryptedChangePayload	JSON	No		AES-GCM envelope when payload is persisted encrypted
syncState	enum	Yes		PENDING, IN_PROGRESS, SYNCED, FAILED, RETRYING

5. IndexedDB Store Map

Store	Primary Key	Secondary Indexes	Notes
students	studentId	status, displayName	Active and deleted records retained
attendanceEvents	eventId	studentId, timestamp, syncState	Immutable attendance history
studentEvents	eventId	studentId, eventType, timestamp, syncState	Immutable student lifecycle history
syncQueue	queueId	syncState, enqueuedAt, retryCount	Pending and retry workloads
sessions	sessionId	userId, expiresAt, state	Session and re-auth gating
users	userId	username(unique), status, role	User lifecycle and login lookup
userEvents	eventId	userId, eventType, timestamp, syncState	Immutable user lifecycle and credential history
localeResources	localeCode:key	localeCode	UI text resources

6. Validation Rules

• Deletion justification is mandatory for DELETE events and must be 10 to 500 characters.
• Unauthorized role actions are rejected and logged.
• Local writes happen before network sync attempts.
• Protected sync submission requires active session.
• Expired session allows local read-only mode, but blocks protected submissions until re-authentication.
• User deactivation is soft only: status becomes DEACTIVATED; physical deletion is forbidden.
• User deactivation guard rules: self-deactivation blocked and last-active ADMIN_USER deactivation blocked.
• UserEvent.changePayload must never contain passwordHash; password changes are recorded as redacted markers.

7. Sync Payload Contract

7.1 Request

{
  "deviceId": "f32d6ce8-0d1a-4ffd-8e8a-3b7f4f0178a9",
  "sentAt": "2026-04-10T12:00:00Z",
  "events": [
    {
      "eventId": "3d9d2f89-8429-4f95-b0ad-6b849bb7e2e0",
      "timestamp": "2026-04-10T12:00:00Z",
      "action": "MARK_PRESENT",
      "studentId": "a1cc3a6a-8501-4fa8-9ea4-0afc7bb8d2f1",
      "actorId": "8aeb4c77-cbc9-47e6-b5aa-67ca967c7a6f",
      "roleId": "ROLL_CALLER",
      "syncState": "PENDING"
    }
  ]
}

7.2 Response

{
  "serverTime": "2026-04-10T12:00:03Z",
  "synced": [
    {
      "eventId": "3d9d2f89-8429-4f95-b0ad-6b849bb7e2e0",
      "serverTimestamp": "2026-04-10T12:00:02Z"
    }
  ],
  "failed": [],
  "conflicts": []
}

7.3 Conflict Response Example

{
  "serverTime": "2026-04-10T12:10:00Z",
  "synced": [],
  "failed": [
    {
      "eventId": "9a6ef640-65fa-4958-b281-c81d7e2fcf7e",
      "reason": "Superseded by admin action",
      "winnerEventId": "4ab6f882-f1f3-416a-b1a0-1237a9ed7f66"
    }
  ],
  "conflicts": [
    {
      "entityId": "a1cc3a6a-8501-4fa8-9ea4-0afc7bb8d2f1",
      "policyApplied": "ADMIN_PRECEDENCE",
      "loserEventId": "9a6ef640-65fa-4958-b281-c81d7e2fcf7e",
      "winnerEventId": "4ab6f882-f1f3-416a-b1a0-1237a9ed7f66"
    }
  ]
}

8. Conflict Resolution Rules

1. If one event actorRole is ADMIN_USER and conflicts with lower-privilege event, admin event wins.
2. If no admin involved, newest serverTimestamp wins.
3. Losing events are retained with isConflictLoser equals true.
4. Every resolved conflict is auditable and visible in history.

9. Compliance and Security Notes

• Sensitive local data must use browser-compatible encryption at rest.
• Sensitive session secrets and mutable event payloads should default to encrypted-at-rest envelopes with key version metadata.
• Access to sensitive data remains role-scoped and action-scoped.
• Device data retention after session expiry must follow read-only policy until re-authentication.
• Audit retention must preserve create, update, delete, and attendance events for compliance review.