Vote sign is inverted between raw storage/API and the participants-votes export (undocumented)

[lgelauff]

Summary. The integer sign of a vote is opposite in the raw database (and the vote API) versus the participants-votes CSV export, and the inversion isn't documented anywhere — only an 8-year-old XXX code comment. This reliably trips up anyone who reads the raw votes table or builds on the vote API. Please confirm that this is correct. I was unable to find a reason for this.

Details.

• Raw votes / votes_latest_unique tables and the vote-submit API use agree = -1, disagree = +1, pass = 0 (matching the web client's Vote enum: Agree: -1, Disagree: 1).
• The participants-votes export flips this to the intuitive agree = +1, disagree = -1 by negating every vote in math/src/polismath/darwin/export.clj:

(defn get-corrected-conversation-votes
  [darwin & args]
  (->> (apply get-conversation-votes* darwin args)
       ;; Flip the signs on the votes XXX (remove when we switch)
       (map #(update-in % [:vote] (partial * -1)))

• That comment ("Flip the signs on the votes XXX (remove when we switch)") has been in place since at least 2018 (0105538), so it looks like the intended "switch" never happened.
• data/Export.md documents the export values (agree = 1) but never mentions that raw storage is the opposite — so a developer querying Postgres directly gets agree = -1 with no warning, and reasonably assumes data corruption or a bug in their own code.

Why it matters. Self-hosters and tool-builders (I'm one) may read the Polis Postgres directly for moderation/analytics rather than going through the CSV export. The silent sign disagreement between the two layers is a genuine footgun — It took quite a deep dive to ascertain why the voting behavior was the way it was.

Asks (in rough priority):

1. Document the inversion — a sentence in Export.md and/or the schema docs stating that raw votes.vote uses agree = -1 and the export negates it. This alone removes the trap.
2. Clarify intent — is the "switch" (normalizing internal storage to agree = +1) still planned, e.g. as part of the Clojure→delphi math migration?
3. Optional migration path — if normalizing outright would break existing consumers, a per-deployment flag (default = current inverted behavior) would let operators opt into sane signs without forcing a breaking change on everyone.

Happy to open a PR for (1) at minimum, but want to verify my correct understanding first. Thanks!

[jucor]

Thanks a lot @lgelauff ! You have indeed stumbled upon the footgun that keeps biting us too. This is a legacy behaviour, from back when 13 years ago polis was working with another metric than agree/disagree, where the semantics of the signs were flipped to those one would expect for agreement (+1) and disagreement (-1). The codebase has various sign-flipping logic scattered everywhere to compensate for this, for example so that the ML code uses the "intuitive" encoding even though the postgres data still stores the "legacy" encoding. The joys of a long-living codebase, warts and all!

We have been discussing how to best remove that footgun. All the new code (e.g. the work on delphi/) uses the "intuitive" encoding, with in theory just the db-polling functions flipping the sign before and after storage. And as you guessed, we would love to normalize the storage to agree = +1 indeed, once delphi replaces the math worker.

But until then, indeed, the footgun remains. We would very much welcome a PR that documents the inversion, thank you for offering !

[lgelauff]

Thanks for the context @jucor !

I tried to take a multi-step approach. The most obvious documentation should happen (imho) at the authoritative location, where there's already a lot of similar comments:

• docs: document the raw vote sign on the votes tables (-1 = Agree) #2555 adds a single uncontroversial comment to 000000_initial.sql where similar comments already exist.
• docs(server): add a data-model orientation doc #2556 attempts to go much further than that: document the entire data model at the database level. Please verify this carefully. I created it with an AI model that learned a lot about this by using it in practice, but that doesn't mean the model (and me, through using it) have a full comprehension of the entire lot.
• In chore(db): remove never-implemented 2013 org/permissions table sketches #2557 I made an attempt to clean up some commented-out code from the sql initialization, that threw off my model initially. From what I can tell, this is all code that doesn't really serve a purpose, but there's likely a lot of context I'm missing here.

[jucor]

Brilliant, thank you ! I have reviewed the three of them, merged two, and made a few minor requests on the third.