Crate taskchampion_sync_server_storage_postgres

This crate implements a Postgres storage backend for the TaskChampion sync server.

Use the PostgresStorage type as an implementation of the Storage trait.

This implementation is tested with Postgres version 17 but should work with any recent version.

Schema Setup

The database identified by the connection string must already exist and be set up with the following schema (also available in postgres/schema.sql in the repository):

CREATE TABLE clients (
	client_id UUID PRIMARY KEY,
	latest_version_id UUID default '00000000-0000-0000-0000-000000000000',
	snapshot_version_id UUID,
	versions_since_snapshot INTEGER,
	snapshot_timestamp BIGINT,
	snapshot BYTEA);

CREATE TABLE versions (
	client_id UUID NOT NULL,
	FOREIGN KEY(client_id) REFERENCES clients (client_id) ON DELETE CASCADE,
	version_id UUID NOT NULL,
	parent_version_id UUID,
	history_segment BYTEA,
	CONSTRAINT versions_pkey PRIMARY KEY (client_id, version_id)
);
CREATE INDEX versions_by_parent ON versions (parent_version_id);

Integration with External Applications

The schema is stable, and any changes to the schema will be made in a major version with migration instructions provided.

An external application may:

• Add additional tables to the database
• Add additional columns to the clients table. If those columns do not have default values, calls to Txn::new_client will fail. It is possible to configure taskchampion-sync-server to never call this method.
• Insert rows into the clients table, using default values for all columns except client_id and application-specific columns.
• Delete rows from the clients table, noting that any associated task data is also deleted.

Structs

PostgresStorage

A storage backend which uses Postgres.