Users and shared books

Usernames, book members and roles, inviting, leaving, and who did what.

• users: unique username, stored lowercase (letters, digits, _ and ., 3 to 30 characters, starting with a letter or digit), an optional display_name, a timezone (sent as a new book’s timezone), language (vi or en, see Languages), is_admin and disabled_at (set only by the admin’s functions; see Sign-in and accounts). No email. The username is also what people sign in with. Usernames can be changed; ids never change. Accounts belong to one server: someone on two servers has two accounts.
• book_members: book, user, role. Whoever creates a book becomes its first owner. The client makes book ids (so a retry can’t make a second book) and the API inserts without RETURNING or ON CONFLICT, since both make Postgres apply the read policy to the new row and the creator only becomes a member once the insert finishes; a retry finds the book first instead.
  • owner: everything, including inviting and removing people, changing roles, book settings and deleting the book.
  • editor: add, edit and delete accounts, categories, people, transactions, budgets and exchange rates.
  • viewer: read only.
• Sharing is in the MVP, among accounts on the same server, with no limit on how many books or people.
• Inviting: an owner adds someone by exact username with a role (Book settings → Members; API). An unknown username and someone already in the book get the same cannot_invite, and invites are rate limited (10 per owner per 15 minutes), because an invite that works shows the username exists. The invitee sees “nguyin invited you to Household” (my_pending_invites(); the inviter is always the owner who sent it) and sees nothing of the book until accepting (accept_invite()). The web app shows pending invites with Accept and Decline on top of every book page and on Create a book, for someone who has no book yet. Owners can change a member’s role and nothing else about the membership, so nobody can push a book onto a stranger, and nobody can raise their own role (an owner can step down while another owner remains).
• Leaving and removal: anyone can leave or decline (declining deletes the invite; nobody is notified, there’s no email); owners can remove people or cancel an invite. Access ends on the next query, including the book’s name and settings (their own activity list keeps their actions but no longer names the book). A book always keeps at least one owner. If the creator leaves or stops being an owner, the book moves to the longest-standing remaining owner (books.created_by).
• Who did what: transactions record created_by and modified_by, invites record invited_by, edit history records changed_by, and the audit log records who acted. The database fills these in from the signed-in user; the app can’t set them. Every “added by” or “edited by” label comes from the book_people view. It names current members, people who have left, and deleted accounts as “Deleted user” (numbered when a book has several).
• Two members editing the same transaction is handled like two devices: one save goes through and the other gets a conflict.