Public Software Group e. V. · LiquidFeedback-Core

LiquidFeedback Core (Backend)

This page is dedicated to the backend of LiquidFeedback (“the core”). You may also want to visit the main page of LiquidFeedback.

The core consists of a database scheme for the PostgreSQL database, including the algorithms for delegations, feedback and the voting procedure implemented as SQL views and database procedures written in PL/pgSQL. As it is licensed under the liberal MIT/X11-License you can include it in any software project you like, as long as there is PostgreSQL support for your programming language available.

Dependencies

PostgreSQL 9.3 or higher
pgLatLon 0.10 (for LiquidFeedback Core v4 or higher)

Releases

Older releases are found here.

Repository

mercurial repository

Changes

2018-11-18: Version 4.1.0
- Support for role accounts
- Changes in table system_application: split-up discovery_baseurl into base_url and manifest_url
- Added unsubscribe_secret column to member table to hold secrets for List-Unsubscribe mail headers
- Several bugfixes
  - Added missing qualifier in expired_token view to avoid authorization codes to be deleted when used
  - Fixed wrong referential integrity index in table ignored_member
  - Fixed error in write_event_initiative_revoked_trigger which caused runtime exceptions
2017-09-29: Version 4.0.0
- Geospatial support
- Dynamic admission quorum
- Absolute issue and initiative quora
- Support for integrated OAuth 2.0 server
- Usage of PostgreSQL's JSONB datatype to store member profile information and settings
- Revised snapshot system
- Extended event logging
- Revised system to store information about accepted terms of use
2016-05-06: Version 3.2.2
- New views member_eligible_to_be_notified and member_to_notify
- Bug fixes
  - Set some notification settings to zero or DEFAULT in functions delete_member and delete_private_data (causes NULL constraint violation otherwise)
  - Exclude locked members from list of recipients in view scheduled_notification_to_send
  - Exclude non-activated members from list of recipients in views scheduled_notification_to_send and newsletter_to_send (relevant only in certain setups)
- Allow to exclude featured initiatives from digests by setting notification_sample_size to zero
2016-04-30: Version 3.2.1
- Removed ON CONFLICT clause to allow PostgreSQL 9.3 compatibility (PostgreSQL 9.3 or 9.4 users can update directly from Core v3.1.0 to v3.2.1)
2016-04-24: Version 3.2.0
- Revised notification system, including newsletters
- Improved error messages (using HINTs)
2015-12-14: Version 3.1.0
- Added min_admission_time field to policy and issue tables
- Removed deprecated member_application table and discussion_url column of initiative table
- Clarified operator precedence
- Shortened constraint names to a maximum length of 63 characters
2015-07-18: Version 3.0.5
- Added support for storing external references (opaque data field) in unit, area, issue, initiative, draft, and suggestion tables
- Error in demo.sql file fixed
2014-07-23: Version 3.0.4
- Added data structures to support externally managed accounts (e.g. LDAP)
2014-07-16: Version 3.0.3
- 2 bugfixes related to first_preference_votes
  - Error in update script from v2.2.6 to v3.0.1 fixed (gets automatically repaired when updating to v3.0.3)
  - Always set first_preference_votes after vote counting (also if no votes are found)
- Added missing information on lf_update_issue_order to README file
2014-07-10: Version 3.0.2
- Configurable complexity of Schulze method
  - Allow two different defeat strengths (tuple of winning votes against losing votes or winning votes only)
  - Tie-breaking according to chapter 5 of Markus Schulze's paper schulze1.pdf
2014-04-06: Version 3.0.1
- Configuration option no_reverse_beat_path declared experimental and disabled by default
- Configuration option no_multistage_majority declared experimental
- Calculation and storage of first_preference_votes
- Reconstruction of missing battle data not created by Core version 1.x
2014-04-06: Version 2.2.6 (backport)
- Configuration option no_reverse_beat_path declared experimental and disabled by default
- Configuration option no_multistage_majority declared experimental
2014-01-31: Version 3.0.0
- Completed minority protection by proportionally ordering issues in admission phase
- Modified tie-breaking
  - Always apply tie-breaking by id (i.e. by order of creation) between those initiatives where the partial order of Schulze yields to a tie
  - As a consequence, field schulze_ranking will already include the results of tie-breaking
2013-08-12: Version 2.2.5
- Added field admin_notice to issue table, allowing publication of correction notices or other administrative notices regarding an issue
- Aborting issues by administrative intervention (new issue state canceled_by_admin)
2013-07-14: Version 2.2.4
- Added support for regular delegation checks and login recovery
- Code cleanup: Removed unnecessary join in event views
2013-03-23: Version 2.2.3
- Code cleanup: Replaced table internal_session_store by table temporary_transaction_data, which is now using the unique 64-bit transaction id as part of its primary key
2013-03-22: Version 2.2.2
- Bugfix regarding an error, which occurred when deleting votes from voters, which lost their voting right during voting phase
2013-03-18: Version 2.2.1
- Proportional ordering of suggestions based on calculations in new background job lf_update_suggestion_order
2013-03-10: Version 2.2.0
- Locking and transaction model rebuilt to avoid deadlocks and improve performance
- Bugfix regarding queries which could not utilize certain database indices (removes a major performance bottleneck for larger installations)
- New feature: Harmonic weighting of initiatives within an issue
2012-10-15: Version 2.1.0
- New polling mode (includes changes in the contingent and privilege tables)
- issue comments removed, voting comments integrated in direct_voter table
- Changed locking behavior to avoid deadlocks
- Better documentation of function delete_private_data()
- Removal of function vote_ratio in update script (was missing in previous update script to version 2.0.12)
2012-10-05: Version 2.0.12
- Removed unwanted (and broken) tie-breaking by approval ratio
2012-06-20: Version 2.0.11
- Simulation of default delegation (i.e. unit delegation for areas, or unit or area delegation for issues) in functions delegation_chain and delegation_info
2012-06-15: Version 2.0.10
- Extended function delegation_info: New field participating_member_id indicating which member in delegation chain is currently participating
2012-05-15: Version 2.0.9
- Better information on delegation chains
  - Function delegation_chain improved
    - Returns unknown participation for other members during voting
    - Support for closed issues by using data from direct_voter and delegating_voter tables
  - New function delegation_info, returning a row containing condensed information about an issue's delegation chain
- Fixes in update script from v1.4.0 to v2.0.0
  - Keep unused invite codes when updating from v1.4.0 to v2.0.0
  - Allow rows in member table containing no name (this backports changes from v2.0.4, but is necessary to keep unused invite codes while updating)
  - Explicitly drop NOT NULL constraint of columns winning_/losing_initiative_id (also fixed when updating to v2.0.9)
- Bug in view unit_member_count fixed, which caused wrong unit member counts to be calculated
- Improved function calculate_ranks, allowing re-counting of votes after issue state has been manually reset
2012-03-15: Version 2.0.8
- Bugfix: Delete member fields lang, notify_level and formatting_engine in function delete_private_data
2012-03-13: Version 2.0.7
- Bugfix: Added lost functionality regarding deletion of sessions:
  - when they expire
  - in function delete_private_data
    (security relevant in certain setups)
  - in function delete_member
2012-03-11: Version 2.0.6
- New column invite_code_expiry in table member
- Bugfix: Do not write history entries for non-activated members
- Bugfix in function write_event_initiative_revoked_trigger regarding initiative_revoked events
2012-03-05: Version 2.0.5
- Bugfix: Delete column admin_comment in table member when executing function delete_private_data
2012-03-01: Version 2.0.4
- Re-added session table to the core
- New column lang in member table
- Changes in event system
  - removed column notify_event_id of member table and added singleton table notification_sent
  - removed view pending_notification and added view selected_event_seen_by_member
- Added index for column occurrence in table event
- Removed generic NOT NULL constraints on columns name and notify_level in table member, to allow non-activated accounts to have NULL values in these columns
- Remove all non-activated members when running function delete_private_data
2012-02-22: Version 2.0.3
- Bugfix regarding unintended NULL values in column eligible of table initiative
- Use vote_ratio function for tie-breaking
- Changed determination of rank for non-winning but admitted initiatives
2012-02-17: Version 2.0.2
- Added column authentication to table member
2012-01-29: Version 2.0.1
- Bugfix in update script from v1.3.1 to v1.4.0 regarding missing default for state column in issue table (updating to v2.0.1 will fix this error too)
- New table unit_setting
- Added column draft_id to table direct_supporter_snapshot
- Bugfix in function delete_private_data: Use invite_code column in member table instead of table invite_code
- Changed/clarified some comments
2011-09-11: Version 2.0.0
- Changes in final vote counting using the Schulze Method
  - Treat the "status quo" as an implicit candidate
  - Configurable implementation of supermajority requirements, including those specified in chapter 7 of Markus Schulze's paper schulze1.pdf
- Allow (wiki) formatting for member statements and suggestions
- Storage of invite codes in table member instead of table invite_code to avoid redundancy
- Removal of vote now/later feature
- Removal of auto-reject feature
- New demo.sql file containing basic settings, units and areas for an imaginary "Solar System" installation
- API related changes
  - Removed session table (it is up to frontends or the API layer to have their own session store)
  - New table "member_application" to be used by the API layer
  - Member deactivation based on last_activity instead of last_login
2011-09-11: Version 1.4.0
- (no changes since release candidate 4)
2011-06-05: Version 1.4.0_rc4 (release candidate 4)
- Bug fixed in function close_voting: Do not create autoreject votes for initiatives which have not been admitted for voting
- Bug fixed in function clean_issue, which caused database error due to constraint violation
- Delete entries from tables issue_comment and voting_comment, when calling function clean_issue
2011-06-02: Version 1.4.0_rc3 (release candidate 3)
- Bug in function close_voting fixed, which caused autoreject votes being inserted, even when members had no voting privilege in the area
2011-05-24: Version 1.4.0_rc2 (release candidate 2)
- Bug in function close_voting fixed, which caused wrong results or database errors
2011-03-13: Version 1.4.0_rc1 (release candidate 1)
- Event log and support for notification system
  - Table event, which is automatically filled by triggers
  - Columns notify_level and notify_event_id in member table
  - Tables ignored_member and ignored_initiative
  - Views event_seen_by_member and pending_notification
- Support for organizational units
  - Table unit stores organizational units as a tree
  - Table privilege selects which members have (voting) rights in a particular organizational unit
  - Global delegations are replaced by unit-delegations
  - References to unit(id) in multiple tables, views and functions
- Deprecated timeline views
- New column state in issue table, tagging each issue with one state of:
  admission, discussion, verification, voting, canceled_revoked_before_accepted, canceled_issue_not_accepted, canceled_after_revocation_during_discussion, canceled_after_revocation_during_verification, calculation, canceled_no_initiative_admitted, finished_without_winner, finished_with_winner
- Changes related to revocation of initiatives
  - New column revoked_by_member_id in table initiative records which member revoked an initiative
  - Revocation of last initiative closes issue without delay, if issue has not been accepted yet
- New tables (rendered_)issue_comment and (rendered_)voting_comment to store comments of members
- Issues do no longer enter rank-calculation phase, if no initiative has passed the second quorum
- Function delete_member deletes sessions to force immediate log-out
- Dropped table ignored_issue
- New table non_voter (partly replacing ignored_issue table) to indicate that somebody does not want to vote him/herself
- Other minor changes
  - INT8 primary key for invite_code table
  - New index contact_other_member_id_idx ON contact(other_member_id)
  - Old update scripts removed from release (only affecting versions before beta26)
  - Included update instructions in README file