Activity

1. bjester

   added a parent issue on Sep 30, 2026

   • Export sync data to an archive file with a disk connection #388
2. bjester

   added the

   Task

   issue type on Sep 30, 2026
3. bjester

   added

   P0 - critical

   TAG: tech update / debt

   TODO: needs clarification

   on Sep 30, 2026
4. bjester

   self-assigned this

   on Sep 30, 2026
5. bjester commented on Oct 7, 2026

   

   bjester

   on Oct 7, 2026

   MemberAuthor

   More actions

   Spike findings: Django-managed archive database (#362)

   Summary

   The design is sound. A runtime alias, MigrationExecutor, bulk_create with explicit tree fields, and incremental auto-vacuum all work on SQLite and PostgreSQL hosts. The custom sqlite3 schema does not need to be reconsidered.

   The spike also found four problems that the spec does not cover. Each one would send archive writes to the host database:

   1. The archive identity write breaks the host identity. DatabaseIDModel.save() runs DatabaseIDModel.objects.update(current=False) through the routed default manager. When the archive's DatabaseIDModel is saved with using=alias, it clears current on the host's database ID.
   2. Routers come before instance state. Model.save() without using and foreign-key traversal (ts.sync_session, cert.parent) call db_for_read/db_for_write first. They use instance._state.db only when every router returns None. The test app's TestingRouter always returns "default", so in the test suite this sends archive reads and writes to the host database. Kolibri's routers return None for morango models, so production falls back to the instance's database.
   3. Certificates need their ScopeDefinition in the archive. Certificate.scope_definition is a foreign key. A bulk_create of the chain fails with IntegrityError unless the ScopeDefinition row exists in the archive. Spec §3.4 does not list ScopeDefinition.
   4. InstanceIDModel has no calculate_uuid(). Instance ids come from sha2_uuid(database_id, system_id, node_id) (morango/models/core.py:172).

   The spike code is kept in this planning worktree at tests/testapp/tests/spike/test_archive_spike.py, and it is not committed. Run it with -s to see the FINDING lines.

   Answers to the spike questions

   All results are the same on both hosts unless a row says otherwise. The hosts are SQLite (the default settings) and PostgreSQL 9.6 (testapp.postgres_settings). The archive is always SQLite. Versions: Django 3.2.25, django-mptt 0.18.0, Python 3.10.

   #	Question	Answer
   1	Can Django 3.2 add an alias at runtime, with full default settings?	Yes. A dict with only ENGINE and NAME gets the full defaults (ATOMIC_REQUESTS, AUTOCOMMIT, CONN_MAX_AGE, OPTIONS, TEST, TIME_ZONE, …). load_backend(connections[alias]) returns the SQLite wrapper on a PostgreSQL host. Assumption 1 holds.
   2	Can MigrationExecutor create all morango tables, without contenttypes?	Yes. The leaf target is ("morango", "0005_self_ref_order"). Every concrete morango table is created, with no other app tables. No pre_migrate/post_migrate signal fires, and no query reaches default. django_migrations gets 29 rows: the squashed migration, all 24 migrations it replaces, and 0002–0005.
   2a	What does a restrictive router do?	It fails silently. If allow_migrate returns False for the alias, Django creates no tables but still records all 29 migrations as applied, and it raises no error. The table check in spec §3.5 step 7 is required.
   3	Does django-mptt query the default database on save(using=alias)?	Yes, and morango does too. Certificate.save (certificates.py:274) wraps every save in _lock_mptt. That lock uses the default connection backend, an un-aliased transaction.atomic(), lock_partitions (Postgres advisory locks on a PostgreSQL host), and parent.get_root(). mptt then runs SELECT MAX(tree_id) on default. If the tree fields are set first, save(using=alias) fails with DatabaseError("Save with update_fields did not affect any rows."). mptt reads go to default too: get_ancestors() on an archive instance queries default and returns [].
   4	Can bulk_create with explicit tree fields write a chain that parent walks can read?	Yes, with zero default queries, if the ScopeDefinition row is in the archive first (finding 3). The spec §3.5 formula gives (lft, rght, level) = (1,6,0), (2,5,1), (3,4,2) for 3 nodes. A parent_id walk through db_manager(alias) reaches the root, and each signature verifies from the archive rows. cert.parent works only when routers return None (finding 2).
   5	Does auto_vacuum=INCREMENTAL before migration let incremental_vacuum shrink the file?	Yes. After migration, auto_vacuum is 2 and journal_mode is delete. Test: 5,000 Buffer rows of 1 KB each. The file is 7.69 MB, stays 7.69 MB after the delete (about 1,827 free pages), and is 0.20 MB after incremental_vacuum. If the pragma is set after the tables exist, auto_vacuum stays 0.
   6	Can the alias be removed with no trace left in connections.databases?	Yes, for the current thread. close(), del connections[alias] and del connections.databases[alias] remove the alias from connections.databases and connections.all(). No -journal file remains, and os.replace works (on Linux). But ConnectionHandler is thread-local: a connection that another thread opened stays open after the alias is removed.
   7	Default-DB guard (spec M0 scope)	It works. connections["default"].execute_wrapper catches ORM queries and savepoints on default, and it ignores alias queries. A bare transaction.atomic() is caught inside a TestCase (SAVEPOINT) on both hosts. Outside a test transaction, it is caught only on SQLite (BEGIN). On PostgreSQL, an empty outermost atomic() sends no SQL.
   8	Empty receiver v2 FSIC shape (plan Task 6)	{"super": {}, "sub": {}}, as Task 6 expects.

   Other findings

   • Plain save() and objects.create() on identity models write to the host. Finding 1 also applies to DatabaseIDManager.create. If the id is set first, save(using=alias) no longer runs the UPDATE, but it still opens a savepoint on default. bulk_create through db_manager(alias) with preset ids writes both identity rows with zero default queries, and the host is unchanged.
   • Receiver helpers rely on routing (finding 2). validate_and_create_buffer_data reads transfer_session.sync_session.profile (sync/utils.py:97) and calls transfer_session.save(update_fields=...) (:147). _update_legacy_self_ref_order reads transfer_session.sync_session.profile (operations.py:748). Plan Task 6 already handles the save. The foreign-key reads are not covered.
   • Kolibri needs no router change (unverified in Kolibri itself). All five Kolibri routers subclass KolibriModelRouter (kolibri/core/utils/model_router.py). That class returns None from db_for_read, db_for_write and allow_migrate for morango models on an archive alias. Spec §3.9 item 1 is therefore already satisfied today. It becomes a constraint to keep, not a change to make.
   • django_migrations lists replaced names. A fresh archive records the squashed migration and the 24 migrations it replaces. Phase 2's compatibility check must accept this.
   • Migration number 0006. 0006_instanceidmodel_system_id already exists among the replaced migrations. A new 0006_archivemanifestentry is legal, but 0006_… is not a unique prefix any more.

   Changes needed

   Spec (.specs/2026-09-29-disk-archive-export-spec.md, gist)

   1. Front matter: update Last Updated, and set Issue/Ticket Reference to Export sync data to an archive file with a disk connection #388.

   2. §2 Assumptions: mark Assumption 1 as verified by M0. Add a new assumption: hosts' routers return None from db_for_read and db_for_write for morango models. Kolibri's routers do. The test app's TestingRouter does not (see §5).

   3. §3.3 rule for converted functions: add two rules:

      • every instance save() and delete() passes using=db.alias;
      • no foreign-key descriptor traversal on archive objects. Use <fk>_id with db.manager(...), or select_related on a pinned queryset.

      Name the known spots: validate_and_create_buffer_data (sync_session.profile, save) and _update_legacy_self_ref_order (sync_session.profile).

   4. §3.4 "Archive contents" table: add a ScopeDefinition row: the scope definition of each certificate in the chain, copied from the host.

   5. §3.5 Archive:

      • Step 6: record that MigrationExecutor emits no signals and sends no queries to default (verified).
      • Step 7: a denying router produces no tables, records migrations as applied, and raises no error. That is why this check exists.
      • Archive instance: write both rows with db.manager(...).bulk_create and preset ids. Never use save() or objects.create(), because DatabaseIDModel.save clears current on the routed default. The instance id = sha2_uuid(database.id, system_id, node_id) (here sha2_uuid(database.id, "", "")), not calculate_uuid().
      • MPTT: correct the rationale. Both morango's Certificate.save (_lock_mptt: default backend, bare atomic, advisory locks) and mptt (SELECT MAX(tree_id), reads) use the default database. A save with preset tree fields fails. write_certificates writes the chain's ScopeDefinition rows first, then the certificates.
      • Unregistration: this is per thread. Open, use, and unregister the alias on one thread.

   6. §3.6 create_sync_session: step (4) also writes the scope definition (through write_certificates). Step (5) creates the archive SyncSession with save(using=archive.db.alias) or the pinned manager. Note that the minted certificate's local save() (step 3) stays on the host, which is correct.

   7. §3.9 item 1: rewrite it as a constraint. Routers must return None (or True) from allow_migrate for archive aliases, and None from db_for_read/db_for_write for morango models. Kolibri's KolibriModelRouter already does both, so Kolibri needs no change, only a check.

   8. §4 Routers bullet: replace "explicit using overrides … allow_migrate is the only router hook involved". Explicit using and pinned managers override routers. Instance save() and foreign-key traversal do not. They follow db_for_* first.

   9. §4 Archive alias registration and threads: add the thread-local connection finding. An open connection on another thread survives unregistration. On Windows, that open handle would also block os.replace.

   10. §4 Constraints inherited by later phases (Phase 2): the django_migrations compatibility check must accept the squashed migration together with its replaced names.

   11. §5 M0 spike: replace the planned list with a short "Findings" summary that links to Spike: prove that a Django-managed archive database works #362.

   12. §5 Default-DB guard and TestingRouter: archive-path tests run with no routers, or with a router that returns None for morango models, under override_settings(DATABASE_ROUTERS=[]). Otherwise TestingRouter hides finding 2. Add a test that the guard catches a bare atomic() inside a TestCase.

   13. §6 Never do: add "Call save() or objects.create() on DatabaseIDModel for the Archive" and "Traverse foreign keys on Archive objects".

   14. §3.10 M0 row: mark it done.

   Plan (.plans/2026-09-30-disk-archive-export.md, gist)

   1. Task 1: check off the steps. Replace Step 7 ("then delete tests/testapp/tests/spike/") with "findings recorded in Spike: prove that a Django-managed archive database works #362. The spike code stays in the planning worktree."
   2. Global constraints, Always do: add two rules:
      • pass using= on every instance save or delete on the archive path, and never traverse foreign keys there;
      • archive-path tests use override_settings(DATABASE_ROUTERS=[]).
   3. Task 4: add a guard test: a bare transaction.atomic() inside the guard raises (savepoint).
   4. Task 6: remove the conditional "If Task 1 found a different empty shape" (confirmed {"super": {}, "sub": {}}). Add transfer_session.sync_session.profile → db.manager(SyncSession) lookup by sync_session_id. Run the default2 tests with override_settings(DATABASE_ROUTERS=[]), because TestingRouter would send default2 instances to default.
   5. Task 5: the same sync_session.profile change in _update_legacy_self_ref_order.
   6. Task 17: add to Step 1: a denying router leaves migrations recorded but no tables. The test asserts MorangoArchiveError (confirmed). Step 3: open and unregister the alias on the calling thread.
   7. Task 19: use bulk_create with preset ids, and id == sha2_uuid(db_id, "", ""). Add a test that the host DatabaseIDModel.current stays True.
   8. Task 20: write_certificates also writes the chain's ScopeDefinition rows (skip ones that exist). Add a test that a chain whose scope definition is missing from the archive still writes.
   9. Task 22: the archive SyncSession is written with the pinned manager. The test asserts the archive holds the ScopeDefinition.
   10. Task 25: the §3.4 contents assertion includes ScopeDefinition.
   11. Task 26: reword the host requirement: routers must return None for morango models on archive aliases (Kolibri already does).

   Issues (online versions are the source of truth)

   • Spike: prove that a Django-managed archive database works #362: post these findings, check off the acceptance criteria, and remove TODO: needs clarification. Replace the third criterion (spike directory deleted) with "spike code kept out of the repository".
   • Explicit database handle for sync database access #365 (Task 4): add the acceptance criterion "the guard fails on a bare transaction.atomic() on default inside a test transaction".
   • Dequeue transferred records into a chosen database #366 (Task 5): in Technical Pointers, note the transfer_session.sync_session.profile read at operations.py:748. Add the criterion "tests with a pinned handle run with no routers".
   • Receiver helpers work against a chosen database #367 (Task 6): note the sync_session.profile read and the save at sync/utils.py:97 and :147. Remove the dependency on the spike's FSIC shape. Add the no-routers testing note.
   • Create and discard an archive database file #378 (Task 17): update Resilience: a denying router produces no tables and raises no error, and the migrations are still recorded (spike result). Add a single-thread note for the alias.
   • Give each archive its own morango instance identity #380 (Task 19): fix the id criterion to sha2_uuid(database_id, "", ""). Require bulk_create (or a pinned manager without save), and give the host current flag as the reason. Add the criterion "the host DatabaseIDModel.current is still True".
   • Store the certificate chain in archives without private keys #381 (Task 20): update Background: both morango's Certificate.save and mptt use the default database. Add ScopeDefinition to the deliverables, with the criterion "a chain whose scope definition is not in the archive is written without an IntegrityError".
   • Start and close sync sessions with a disk connection #383 (Task 22): the archive holds the ScopeDefinition, and the archive SyncSession is written to the archive alias.
   • Prove that an exported archive matches a network server #386 (Task 25): the contents table now includes ScopeDefinition.
   • Document disk export and deprecations in the changelog #387 (Task 26): reword the host-router item: routers must return None for morango models on archive aliases. Kolibri already does.
   • Start and close sync sessions with a disk connection #383, Receive pushed data into an archive through the Peer protocol #384, Prove that an exported archive matches a network server #386, Export sync data to an archive file with a disk connection #388: each one has the same "Kolibri companion changes" note. Reword it to match the new §3.9 item 1: the router requirement covers db_for_read/db_for_write as well as allow_migrate, and Kolibri's current routers already meet it. In Export sync data to an archive file with a disk connection #388, also mark M0 done.
6. bjester

   removed

   TODO: needs clarification

   on Oct 7, 2026
7. bjester

   closed this as completedon Oct 8, 2026