Client Architecture

On the client, the application defines a schema with tables, columns and indexes. These are then usable as if they were actual SQLite tables, while in reality these are created as SQLite views.

The client SDK maintains the following tables:

1. ps_data__<table> This contains the data for <table> , in JSON format. This table's schema does not change when columns are added, removed or changed.

2. ps_data_local__<table> Same as the above, but for local-only tables.

3. <table> (VIEW) - this is a view on the above table, with each defined column extracted from the JSON field. For example, a "description" text column would be CAST(data ->> '$.description' as TEXT).

4. ps_untyped - Any synced table that does is not defined in the client-side schema is placed here. If the table is added to the schema at a later point, the data is then migrated to ps_data__<table>.

5. ps_oplog - This is data as received by the PowerSync service, grouped per bucket.

6. ps_crud - The local upload queue.

7. ps_buckets - A small amount of metadata for each bucket.

8. ps_migrations - Table keeping track of SDK schema migrations.

Most rows will be present in at least two tables — the ps_data__<table> table, and in ps_oplog. It may be present multiple times in ps_oplog, if it was synced via multiple buckets.

The copy in ps_oplog may be newer than the one in ps_data__<table>. Only when a full checkpoint has been download, will the data be copied over to the individual tables. If multiple rows with the same table and id has been synced, only one will be preserved (the one with the highest op_id).