GitHub - beeper/desktop-api-sql: Query your Beeper Desktop data with PostgreSQL

Note

The Beeper Desktop API PostgreSQL Extension was an experiment and we are no longer maintaining it.

The Beeper Desktop API PostgreSQL Extension provides convenient access to the Beeper Desktop REST API from PostgreSQL.

The REST API documentation can be found on developers.beeper.com.

Installation

Clone the repository:

git clone git@github.com:beeper/desktop-api-sql.git
cd desktop-api-sql

Install the extension:

Load it into the relevant database:

CREATE EXTENSION IF NOT EXISTS plpython3u; -- Dependency
CREATE EXTENSION beeper_desktop_api;

And install the Python SDK dependency:

# install from the production repo
pip install git+ssh://git@github.com/beeper/desktop-api-python.git

See ./scripts/test how to use a Python virtual environment if you prefer that instead.

Use the troubleshooting section if you encounter issues during or after installation.

Requirements

This extension requires:

PostgreSQL 14 or higher
PL/Python
Python 3.9 or higher
The beeper_desktop_api Python package

Usage

SELECT *
FROM beeper_desktop_api_chats.search(
  account_ids := ARRAY[
    'matrix', 'discordgo', 'local-whatsapp_ba_EvYDBBsZbRQAy3UOSWqG0LuTVkc'
  ],
  include_muted := TRUE,
  "limit" := 3,
  type := 'single'
);

Client configuration

Configure the client by setting configuration parameters at the database level:

ALTER DATABASE my_database SET beeper_desktop_api.access_token = 'My Access Token';

Note

ALTER DATABASE persistently alters the database, but doesn't take effect until the next session. To ephemerally modify the current session, use SET beeper_desktop_api.access_token TO 'My Access Token';.

See this table for the available configuration parameters:

Parameter	Required	Default value
`beeper_desktop_api.access_token`	false	-
`beeper_desktop_api.base_url`	false	`'http://localhost:23373'`

Requests and responses

To send a request to the Beeper Desktop API, call the relevant SQL function with values corresponding to the parameter types and SELECT the columns you need from the returned rows.

To construct composite type parameters, use the parameter type's provided make_* function. For example, beeper_desktop_api_matrix_rooms.join_params_third_party_signed may be constructed like so:

beeper_desktop_api_matrix_rooms.make_join_params_third_party_signed(
  token := 'random8nonce',
  mxid := 'bob',
  sender := 'alice',
  signatures := $$
  {
    "example.org": {
      "ed25519:0": "some9signature"
    }
  }
  $$::JSONB
)

Pagination

For Beeper Desktop API endpoints that return a paginated lists of results, the extension automatically fetches more pages as needed.

For example, the following query will make the minimum number of requests necessary to satisfy the LIMIT:

SELECT *
FROM beeper_desktop_api_messages.search(
  account_ids := ARRAY[
    'discordgo', 'local-whatsapp_ba_EvYDBBsZbRQAy3UOSWqG0LuTVkc'
  ],
  "limit" := 10,
  query := 'oauth'
)
LIMIT 200;

Important

Place your LIMIT as close to the paginated function call as possible. If the LIMIT is too far removed, then PostgreSQL may not push down the condition, causing all pages to be requested and buffered.

Caching

Sending requests to the Beeper Desktop API for every SQL query can be slow. Combine materialized views with pg_cron for scheduled data pulls:

CREATE MATERIALIZED VIEW beeper_desktop_api_messages AS
SELECT *
FROM beeper_desktop_api_messages.search(
  account_ids := ARRAY[
    'discordgo', 'local-whatsapp_ba_EvYDBBsZbRQAy3UOSWqG0LuTVkc'
  ],
  "limit" := 10,
  query := 'oauth'
);

-- Refresh the view every 4 hours.
SELECT cron.schedule(
  'refresh-beeper-desktop-api-messages',
  '0 */4 * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY beeper_desktop_api_messages'
);

Troubleshooting

Installation

If you encounter an error such as:

Then run with sudo. If necessary, ensure your terminal has full disk access.

If you encounter an error such as:

make: pg_config: Command not found

Then ensure you have pg_config installed and in your PATH. If necessary, tell make where to find it:

PG_CONFIG=/path/to/pg_config make install

To install the extension in a custom prefix on PostgreSQL 18 or later, pass the prefix argument:

make install prefix=/usr/local/extras

You must also ensure that the prefix is included in the following postgresql.conf parameters:

extension_control_path = '/usr/local/extras/postgresql/share:$system'
dynamic_library_path   = '/usr/local/extras/postgresql/lib:$libdir'

Loading

If you encounter an error such as:

ERROR: could not load library

Then ensure your Python installation is linked to the directory where PostgreSQL was looking for it. You can print out the directory of your Python installation with this command:

python3 -c "import sys; print(sys.prefix)"

Semantic versioning

This package generally follows SemVer conventions, though certain backwards-incompatible changes may be released as minor versions:

Changes to library internals which are technically public but not intended or documented for external use. (Please open a GitHub issue to let us know if you are relying on such internals.)
Changes that we do not expect to impact the vast majority of users in practice.

We take backwards-compatibility seriously and work hard to ensure you can rely on a smooth upgrade experience.

We are keen for your feedback; please open an issue with questions, bugs, or suggestions.