Student Psychological Literacy Privacy, FERPA Scoping & Audit Logging

Implement FERPA-compliant access scoping, audit logging, and soft deletes

1. Problem Statement

The Autonomy Protocol operates within public high schools. High school students record personal reflections, behavioral liability inventories, and conflict resolution mappings. Under FERPA and COPPA:

• Student educational and behavioral records must be strictly isolated by school and authorized educators.
• Any modification of student progress, records, or account roles must have a non-repudiable audit trail.
• Accidental hard deletes must be prevented in favor of verifiable retention policies.

2. Target Schema Additions

export const auditLogs = sqliteTable("audit_logs", {
  id: text("id").primaryKey(),
  actorId: text("actor_id").notNull().references(() => user.id),
  action: text("action").notNull(), // e.g., "user.role_change", "submission.grade", "progress.purge"
  targetEntity: text("target_entity").notNull(), // e.g., "student_submission"
  targetId: text("target_id").notNull(),
  districtId: text("district_id").references(() => districts.id),
  metadata: text("metadata"), // JSON delta/context
  ipAddress: text("ip_address"),
  userAgent: text("user_agent"),
  timestamp: integer("timestamp", { mode: "timestamp_ms" }).notNull(),
}, (table) => [
  index("audit_actor_idx").on(table.actorId),
  index("audit_target_idx").on(table.targetEntity, table.targetId),
  index("audit_timestamp_idx").on(table.timestamp),
]);

Review the proposed auditLogs schema alongside the existing user, district, submission, progress, and deletion models. Completion requires school and educator access isolation, non-repudiable change logging, and soft-delete or retention behavior covering the FERPA and COPPA requirements.