Database Schema

User

• id: ID
• username: string (unique, case-insensitive, alphanumeric_-)
• password: string (salted hash)
• email: string (unique, valid email)
• unverified_email: string
• email_verified_at: datetime
• created_at: datetime
• edited_at: datetime
• last_login_at: datetime
• description: blob
• link: string (ensure valid URL if possible, only using http/https)
• timezone: string (must be a tz: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones)
• public: boolean

Project

• id: ID
• user: fk(User)
• created_at: datetime
• edited_at: datetime
• title: string
• details: json {description, genre}
• public: boolean (default to what the user's privacy setting is)

ProjectGoal

• id: ID
• project: fk(Project)
• type: string (support writing or editing in frontend)
• units: string (default options: words, hours, days, pages, chapters)
• current_value: number (decimal, 0.00 precision, ideally a calculated column if mysql supports it, otherwise cache it here)
• goal: number (decimal, 0.00 precision)
• created_at: datetime
• edited_at: datetime
• start_date: date (in user's local time)
• end_date: date (in user's local time) Should start/end time be on Project or ProjectGoal?

ProjectGoalProgress (only one of these should exist per project per day for MVP)

• id: ID
• goal: fk(ProjectGoal)
• created_at: datetime
• entry_date: date
• value: number (decimal, 0.00 precision)

Report

• id: ID
• path: string (e.g. /user/test)
• from_user: fk(User)
• created_at: datetime
• notes: blob (e.g. the notes/description of the report that's submitted; can be left out of MVP)

LoginToken

• id: ID
• secret: string (64 random characters? case insensitive. That should be enough to prevent collisions.)
• created_at: datetime
• expires_at: datetime
• user: fk(User)
• type: string (password-reset, email-verify, login, etc.)
• payload: blob (e.g. the email address that's being verified)