-- Profile: about text, IANA timezone, and avatars stored in the database. Applied by
-- store/migrate.rs. Existing users get about = '' and timezone = 'UTC'.

ALTER TABLE users ADD COLUMN about TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN timezone TEXT NOT NULL DEFAULT 'UTC';

CREATE TABLE avatars (
    user_id     TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    data        BLOB NOT NULL,          -- re-encoded PNG, 256x256
    hash        TEXT NOT NULL,          -- hex sha256 of data (first 16 chars), used as ETag and as ?v=
    updated_at  TEXT NOT NULL
);