"""Domain schema. Every schema change is an Alembic migration — never edit a table shape here without one. Three table families live in one metadata: * easel's own domain (projects/screens/options/mockups/pins/walkthrough/events) * the Interaction Standard embryo (thread/comment/attachment/notifications) — shapes kept byte-compatible with caddie's 04-interaction-standard.md §4/§5 so the future `bw_interaction` kit swap is a substitution, not a migration * the caddie seam (`caddie_links`) — inert until the M2 drop-in arrives The kit's four `bw_*` tables are added to the same metadata at import time by accounts.py (managed store), so Alembic sees the complete schema. """ import secrets from datetime import datetime, timezone from sqlalchemy import ( false, JSON, Boolean, DateTime, Float, ForeignKey, Integer, String, Text, UniqueConstraint, Uuid, ) from sqlalchemy.orm import Mapped, mapped_column, relationship from app.db import Base def utcnow() -> datetime: return datetime.now(timezone.utc) def new_file_id() -> str: return secrets.token_hex(16) # --------------------------------------------------------------- easel domain class ProjectDetails(Base): """1:1 extension of a kit instance (the instance id IS the project id). Domain fields the kit's instance row doesn't carry.""" __tablename__ = "project_details" instance_id: Mapped[str] = mapped_column(String(64), primary_key=True) description: Mapped[str] = mapped_column(Text, default="") # with-satellite convention: store the party id; never a name as truth. client_party_id: Mapped[str | None] = mapped_column(Uuid(as_uuid=False), nullable=True) # caddie seam: the hub's assignment linkage lands here at M2. external_ref: Mapped[str | None] = mapped_column(String(128), nullable=True) # NULL until the project is presented to the client; drives the rollup's # in_progress -> waiting_client edge. sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) # The client's "Feedback complete" for the current round: set when they # press it, cleared by the next present. The rollup reads it as the team's # turn. Who pressed it is recorded because a firm can have several voices. feedback_completed_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True) feedback_completed_by: Mapped[str | None] = mapped_column(String(64), nullable=True) # The team's own words for the tool introduction's points, per point key: # {"stages": {"title": "...", "body": "..."}}. The points themselves ship # with the app (frontend lib/toolTour.ts); a project may say them its way. tour_overrides: Mapped[dict | None] = mapped_column(JSON(none_as_null=True), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) class ProjectStage(Base): """A named phase of the project's roadmap — caddie's `stages` shape (01-vision §5: position/title/body_md/status/started_at/closed_at, client_visible, `key` as source_template_key), held here per project until the hub owns it. NOT a state machine: any number may be active and each closes on its own. easel draws the client's timeline from these and shows its own work inside the active one.""" __tablename__ = "project_stages" __table_args__ = (UniqueConstraint("instance_id", "key"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) instance_id: Mapped[str] = mapped_column(String(64), index=True) key: Mapped[str] = mapped_column(String(64)) position: Mapped[int] = mapped_column(Integer, default=0) title: Mapped[str] = mapped_column(String(200)) body_md: Mapped[str] = mapped_column(Text, default="") status: Mapped[str] = mapped_column(String(16), default="planned") # planned|active|done|skipped client_visible: Mapped[bool] = mapped_column(Boolean, default=True) started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) closed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) class Screen(Base): __tablename__ = "screens" __table_args__ = (UniqueConstraint("instance_id", "slug"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) instance_id: Mapped[str] = mapped_column(String(64), index=True) title: Mapped[str] = mapped_column(String(200)) slug: Mapped[str] = mapped_column(String(80)) position: Mapped[int] = mapped_column(Integer, default=0) # The direction-locking act: the client's chosen option for this screen. # DRAFT vs PUBLISHED. A screen being worked on is ours alone; the client # sees it when we say so. New screens start as drafts — you cannot # accidentally show someone half a page — while everything that already # existed was visible and stays visible (the migration backfills true). published: Mapped[bool] = mapped_column(Boolean, default=False) # The variant values chosen alongside the option — {"brand": "hive"}. The # direction is the option AND its variants: Adi's closing note asks the # client for "an opening AND a brand level", which is two decisions, so a # selection that recorded only the option would be half an answer. selected_variants: Mapped[dict | None] = mapped_column( JSON(none_as_null=True), nullable=True) selected_option_id: Mapped[int | None] = mapped_column( ForeignKey("options.id", use_alter=True, name="fk_screens_selected_option"), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) options: Mapped[list["Option"]] = relationship( back_populates="screen", foreign_keys="Option.screen_id", order_by="Option.position", cascade="all, delete-orphan") class Option(Base): __tablename__ = "options" id: Mapped[int] = mapped_column(Integer, primary_key=True) screen_id: Mapped[int] = mapped_column(ForeignKey("screens.id"), index=True) title: Mapped[str] = mapped_column(String(200)) concept_tag: Mapped[str] = mapped_column(String(120), default="") # A line the client reads on the board: what this concept represents. blurb: Mapped[str] = mapped_column(Text, default="") # An uploaded picture of the concept (data/uploads/option-/), # served through the attachments rules. A live frame at card size is a # silhouette; a picture the team chose says what the concept is. thumbnail_name: Mapped[str | None] = mapped_column(String(64), nullable=True) position: Mapped[int] = mapped_column(Integer, default=0) # rel_path of the bundle's entry HTML (usually "index.html"). entry_path: Mapped[str] = mapped_column(String(255), default="index.html") created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) screen: Mapped[Screen] = relationship(back_populates="options", foreign_keys=[screen_id]) files: Mapped[list["MockupFile"]] = relationship( back_populates="option", cascade="all, delete-orphan") variants: Mapped[list["OptionVariant"]] = relationship( cascade="all, delete-orphan", order_by="OptionVariant.axis, OptionVariant.position, OptionVariant.id") class MockupFile(Base): """One stored file of an option's bundle. `rel_path` is the as-uploaded relative path ("index.html", "assets/style.css") the serving route resolves; `stored_name` is the random on-disk name under data/mockups//.""" __tablename__ = "mockup_files" __table_args__ = (UniqueConstraint("option_id", "rel_path"),) id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_file_id) option_id: Mapped[int] = mapped_column(ForeignKey("options.id"), index=True) rel_path: Mapped[str] = mapped_column(String(255)) stored_name: Mapped[str] = mapped_column(String(64)) content_type: Mapped[str] = mapped_column(String(100)) size: Mapped[int] = mapped_column("bytes", Integer) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) option: Mapped[Option] = relationship(back_populates="files") class Pin(Base): """The x/y anchor beside a standard-shaped thread (one thread per pin — subject_type "pin", subject_id = this row's id as text). The anchor rides here so the interaction tables stay byte-compatible with the standard.""" __tablename__ = "pins" id: Mapped[int] = mapped_column(Integer, primary_key=True) option_id: Mapped[int] = mapped_column(ForeignKey("options.id"), index=True) thread_id: Mapped[int] = mapped_column(ForeignKey("thread.id"), unique=True) x_percent: Mapped[float] = mapped_column(Float) y_percent: Mapped[float] = mapped_column(Float) # An AREA note: the width/height (document %) of the region the remark is # about, anchored at x/y as its top-left. NULL = a point. Highlighted like # a walkthrough beat while the note is open. w_percent: Mapped[float | None] = mapped_column(Float, nullable=True) h_percent: Mapped[float | None] = mapped_column(Float, nullable=True) # Where the note sits ON THE DESIGN: {"s": selector, "fx", "oy"} names the # mockup element under the point, the point's place across it (0..1) and # its distance down from the element's top (CSS px); an area adds # "s2"/"fx2"/"oy2" for its bottom-right corner. The # percentages above move whenever the page changes height (an accordion # opening above the note), so the viewer measures this instead and keeps # x/y as the fallback for an element it cannot find. anchor: Mapped[dict | None] = mapped_column(JSON(none_as_null=True), nullable=True) locked: Mapped[bool] = mapped_column(Boolean, default=True) created_by: Mapped[str] = mapped_column(String(64)) # Which variation was showing when this note was left, as {"brand":"hive"}. # A pin is a point on a DESIGN, and when a toggle changes the design the # point can mean something different — "make this bigger" against the # hexagon header is not the same remark as against the plain one. Null means # the option carried no variations at the time. variants: Mapped[dict | None] = mapped_column(JSON(none_as_null=True), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) thread: Mapped["Thread"] = relationship() class WalkthroughStep(Base): """A guided-presentation beat on one option. Target is a CSS selector into the mockup document (resolved by the bridge); `rect` is a fallback normalized box {x,y,w,h} in percent for when no stable selector exists.""" __tablename__ = "walkthrough_steps" id: Mapped[int] = mapped_column(Integer, primary_key=True) option_id: Mapped[int] = mapped_column(ForeignKey("options.id"), index=True) step_order: Mapped[int] = mapped_column(Integer, default=1) title: Mapped[str] = mapped_column(String(200)) body_md: Mapped[str] = mapped_column(Text, default="") target_selector: Mapped[str] = mapped_column(String(255), default="") rect: Mapped[dict | None] = mapped_column(JSON(none_as_null=True), nullable=True) requires_approval: Mapped[bool] = mapped_column(Boolean, default=False) # A DEMO CLICK. Some designs only explain themselves when something is # opened — clicking a practice area reveals that team's takeover — and # describing that in words is a poor substitute for showing it. The bridge # dispatches the click inside the frame, on the page's own element. click_selector: Mapped[str] = mapped_column(String(255), default="") # Seconds the beat is READ before the design demonstrates itself. It fired # a quarter of a second after the beat opened, which is before anyone has # read the words explaining what they are about to see. Per point, because # it depends on the sentence. click_delay_seconds: Mapped[int] = mapped_column(Integer, default=2, server_default="2") # Seconds before the bridge hands the design back (Escape, then a background # click). 0 means leave it open — sometimes the revealed state IS the point. click_dismiss_seconds: Mapped[int] = mapped_column(Integer, default=3) # Said in a centred card over the dimmed design, with no highlight: a # welcome, or a word about the whole design before the points on it. modal: Mapped[bool] = mapped_column(Boolean, default=False, server_default=false()) created_by: Mapped[str] = mapped_column(String(64)) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) class StepApproval(Base): __tablename__ = "step_approvals" __table_args__ = (UniqueConstraint("step_id", "username"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) step_id: Mapped[int] = mapped_column(ForeignKey("walkthrough_steps.id"), index=True) username: Mapped[str] = mapped_column(String(64)) status: Mapped[str] = mapped_column(String(30), default="approved") comment: Mapped[str | None] = mapped_column(Text, nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) class Event(Base): """Append-only history of state changes (sent, selection, resolve, approval). INSERT-only by convention — nothing in the app updates or deletes a row.""" __tablename__ = "events" id: Mapped[int] = mapped_column(Integer, primary_key=True) instance_id: Mapped[str] = mapped_column(String(64), index=True) actor: Mapped[str] = mapped_column(String(64)) kind: Mapped[str] = mapped_column(String(50)) payload: Mapped[dict | None] = mapped_column(JSON(none_as_null=True), nullable=True) occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) # ------------------------------------------- Interaction Standard embryo (§4) class Thread(Base): __tablename__ = "thread" __table_args__ = (UniqueConstraint("subject_type", "subject_id"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) project_id: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True) subject_type: Mapped[str] = mapped_column(String(40)) subject_id: Mapped[str] = mapped_column(String(64)) title: Mapped[str] = mapped_column(String(255), default="") resolved: Mapped[bool] = mapped_column(Boolean, default=False) created_by: Mapped[str] = mapped_column(String(64)) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) comments: Mapped[list["Comment"]] = relationship( back_populates="thread", order_by="Comment.created_at", cascade="all, delete-orphan") class Comment(Base): __tablename__ = "comment" id: Mapped[int] = mapped_column(Integer, primary_key=True) thread_id: Mapped[int] = mapped_column(ForeignKey("thread.id"), index=True) body_md: Mapped[str] = mapped_column(Text) author_username: Mapped[str] = mapped_column(String(64)) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) edited_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) # Soft delete only — never hard-delete a conversation (standard §4). deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) thread: Mapped[Thread] = relationship(back_populates="comments") attachments: Mapped[list["Attachment"]] = relationship( back_populates="comment", cascade="all, delete-orphan") reactions: Mapped[list["CommentReaction"]] = relationship( order_by="CommentReaction.id", cascade="all, delete-orphan") class CommentReaction(Base): """One person's emoji on a comment, Slack's way: a quick "seen it, agreed" that needs no reply. One row per person per emoji; pressing it again takes it back. Not a notification, because acknowledging is not news.""" __tablename__ = "comment_reactions" __table_args__ = (UniqueConstraint("comment_id", "username", "emoji"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) comment_id: Mapped[int] = mapped_column( ForeignKey("comment.id", ondelete="CASCADE"), index=True) username: Mapped[str] = mapped_column(String(64)) emoji: Mapped[str] = mapped_column(String(16)) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) class CommentRevision(Base): """A superseded version of a comment. An edit that can silently rewrite the record is the wrong thing in a client review — "you said X" / "no, I said Y" is precisely the dispute this work exists to prevent. So anyone can fix their own mistake, and nobody can make the earlier wording disappear: each edit files the text it replaced, and everyone who can read the comment can read what it used to say. Append-only. Rows are written on edit and never touched again. """ __tablename__ = "comment_revisions" id: Mapped[int] = mapped_column(Integer, primary_key=True) comment_id: Mapped[int] = mapped_column(ForeignKey("comment.id", ondelete="CASCADE"), index=True) body_md: Mapped[str] = mapped_column(Text) # When this wording STOPPED being the current one. replaced_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) class Attachment(Base): __tablename__ = "attachment" id: Mapped[int] = mapped_column(Integer, primary_key=True) comment_id: Mapped[int] = mapped_column(ForeignKey("comment.id"), index=True) stored_name: Mapped[str] = mapped_column(String(64)) original_name: Mapped[str] = mapped_column(String(255)) content_type: Mapped[str] = mapped_column(String(100)) size: Mapped[int] = mapped_column("bytes", Integer) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) comment: Mapped[Comment] = relationship(back_populates="attachments") class Notification(Base): """The §5 event shape, landed locally (the inbox service does not exist yet — this table is the local-only mode caddie M1 also runs). `delivered_at` is the §7 outbound-delivery seam, deliberately unwritten-to.""" __tablename__ = "notifications" id: Mapped[int] = mapped_column(Integer, primary_key=True) recipient: Mapped[str] = mapped_column(String(64), index=True) actor: Mapped[str] = mapped_column(String(64)) app: Mapped[str] = mapped_column(String(40)) kind: Mapped[str] = mapped_column(String(20)) # mention·reply·turn·status·resolved category: Mapped[str] = mapped_column(String(60), default="") context_label: Mapped[str] = mapped_column(String(120), default="") body: Mapped[str] = mapped_column(String(200), default="") url: Mapped[str] = mapped_column(String(500)) source_type: Mapped[str] = mapped_column(String(40)) source_id: Mapped[str] = mapped_column(String(64)) project_id: Mapped[str | None] = mapped_column(String(64), nullable=True) dedupe_key: Mapped[str] = mapped_column(String(160), unique=True) occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) read_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) delivered_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) class OptionVariant(Base): """A togglable variation WITHIN an option. rian's correction to the six-option plan: a concept is two options plus variants (the hexagon decorations, the header configs), not one option per combination. That makes every comparison one-variable, which is the whole point of the direction gate — Adi's own notes flagged that `v1..v6` ordering changed the opening AND the brand at once, "the hardest kind of comparison to hold in your head". A variant is expressed as a CSS class toggled on `` by the bridge, so one bundle serves every combination and toggling costs no reload — the client keeps their scroll position while comparing, which is the entire reason to prefer this over separate files. `axis` groups mutually exclusive choices ("brand": quiet | hive). An option may carry several axes; the client picks one value on each. """ __tablename__ = "option_variants" __table_args__ = (UniqueConstraint("option_id", "axis", "key", name="uq_option_variant"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) option_id: Mapped[int] = mapped_column(ForeignKey("options.id", ondelete="CASCADE"), index=True) axis: Mapped[str] = mapped_column(String(40)) # "brand" axis_label: Mapped[str] = mapped_column(String(80), default="") # "Brand" key: Mapped[str] = mapped_column(String(40)) # "hive" label: Mapped[str] = mapped_column(String(80)) # "Full hive" css_class: Mapped[str] = mapped_column(String(60)) # "v-brand-hive" position: Mapped[int] = mapped_column(Integer, default=0) is_default: Mapped[bool] = mapped_column(Boolean, default=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) # --------------------------------------------------------------- onboarding class TourProgress(Base): """Whether a person has been walked through the TOOL on a given board. Deliberately the only thing about the tool tour that is persisted. The tour's steps live in the frontend as data (`lib/toolTour.ts`) with a version constant, exactly like the hartlingowners precedent rian pointed at: content is edited and the version bumped, and everyone below that version is offered it again. Steps in a table would buy an authoring UI nobody asked for and a migration every time a sentence changes. Keyed per person PER BOARD (rian's "once per person per board"), so a client added to a second project is oriented there too rather than assumed fluent. """ __tablename__ = "tour_progress" __table_args__ = (UniqueConstraint("instance_id", "username", name="uq_tour_progress_who"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) instance_id: Mapped[str] = mapped_column(String(64), index=True) username: Mapped[str] = mapped_column(String(64), index=True) # Highest tour version this person has finished or dismissed on this board. seen_version: Mapped[int] = mapped_column(Integer, default=0) # Whether they ran it to the end (vs skipped) — content signal, not a gate. completed: Mapped[bool] = mapped_column(Boolean, default=False) updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) class ProjectVisit(Base): """When this person last looked at this board. Deliberately its own table rather than a column on `tour_progress`: that row answers "have they been walked through the tool", this one answers "what has changed since they were last here". Two questions, and folding them together would leave a table whose name lied about half its contents. The engagement guarantees a second visit — the homepage is decided, then the remaining pages are designed against it — so "what is new?" is the first question a returning client has, and nothing answered it. """ __tablename__ = "project_visits" __table_args__ = (UniqueConstraint("instance_id", "username", name="uq_project_visit_who"),) id: Mapped[int] = mapped_column(Integer, primary_key=True) instance_id: Mapped[str] = mapped_column(String(64), index=True) username: Mapped[str] = mapped_column(String(64), index=True) # Two different clocks, deliberately. `last_seen_at` is the NEWNESS marker # and only moves when the board is deliberately marked seen; `last_active_at` # is the heartbeat and moves constantly. Sharing one column would mean every # heartbeat silently cleared the "new since your last visit" flags out from # under someone who had not scrolled to them yet. last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) last_active_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True) class AppSetting(Base): """App-wide switches. One row per key; there is exactly one today.""" __tablename__ = "app_settings" key: Mapped[str] = mapped_column(String(60), primary_key=True) value: Mapped[str] = mapped_column(String(200), default="") updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) class OutboundMessage(Base): """Every email easel decides to send — sent or not. An outbox rather than a fire-and-forget call, for two reasons. It is how the email design is testable BEFORE a transport exists: you can see exactly what would have gone to whom, and read it, without anything leaving the building. And it is how Dev Mode is more than a switch — a suppressed message is still recorded, so "we turned it off and nothing was lost" is a thing you can check rather than hope. `sent_at` is the Interaction Standard's §7 delivery seam, finally written to. """ __tablename__ = "outbound_messages" id: Mapped[int] = mapped_column(Integer, primary_key=True) recipient: Mapped[str] = mapped_column(String(64), index=True) to_email: Mapped[str] = mapped_column(String(200), default="") kind: Mapped[str] = mapped_column(String(40)) # presented · replied subject: Mapped[str] = mapped_column(String(200)) body: Mapped[str] = mapped_column(Text, default="") url: Mapped[str] = mapped_column(String(500), default="") project_id: Mapped[str | None] = mapped_column(String(64), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow) sent_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) # Why it did not go: "dev_mode" · "no_transport" · "no_address". held_reason: Mapped[str] = mapped_column(String(40), default="") # ------------------------------------------------------------- caddie seam class CaddieLink(Base): """Tool-contract §2 mapping (assignment -> instance + participants). Written by the M2 drop-in; until then only the shape exists so migrations don't churn when the contract lands.""" __tablename__ = "caddie_links" assignment_id: Mapped[str] = mapped_column(String(64), primary_key=True) instance_id: Mapped[str] = mapped_column(String(64), index=True) participants: Mapped[list | None] = mapped_column(JSON(none_as_null=True), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utcnow)