# Building CI/CD for Oracle APEX – Part 3: When Can I Trust a Snapshot? At the end of [Part 2](https://davidpataki.com/building-ci-cd-for-oracle-apex-part-2-when-different-ddl-hashes-don-t-mean-different-database-objects), I had reached a more careful way of comparing DDL. Different hashes do not automatically mean different database objects. The comparison rules need to be explicit, versioned and appropriate for the object type. But that conclusion is only useful if I can trust the source and target states being compared. If a snapshot is incomplete, if two jobs try to import the same source at once, or if a failed transfer moves its checkpoint too early, a perfectly designed hash rule cannot make the result reliable. Before I can turn differences into a deployment plan, I need to know that the differences are based on complete and traceable states. This part is about how I approached that problem. ## A snapshot is a contract, not just a query result It is easy to think of a database snapshot as a set of queries against the data dictionary. For this project, that was not enough. A snapshot needs to make a stronger claim: - It identifies the source database, application system and schema or APEX application it represents. - It has a clear outcome: complete, warning, failed or still in progress. - The central TMS can verify that it received the expected details. - A failed import can be repeated without creating a second copy or skipping data. The system collects three related but different kinds of information from DEV, TEST and PROD. DDL events record individual structural changes. Schema snapshots record objects, DDL, dependencies, grants and comments. APEX snapshots record the component state of an application, while a separate stream records Builder activity. Those streams answer different questions, so they cannot safely share one generic checkpoint. An event stream is incremental. A completed snapshot is a named, immutable state. Treating both as if they were simply “the latest data” was one of the design shortcuts I had to remove. ![](https://cdn.hashnode.com/uploads/covers/6461f51b60bafcc7fa959d8c/676d1578-13be-4a33-b375-ab8e9976033c.png align="center") *Figure 1. A successful status means that the source state was collected and accepted by the central audit. It does not merely mean that a scheduler job started.* ## I do not freeze development to take a snapshot One of the questions I had to answer was what should happen if an object changes while a snapshot is being collected. I did not want the audit process to lock application schemas or turn normal development into a waiting exercise. The purpose of the system is to observe the environments, not to take ownership of them. That means a snapshot is not a magical, global freeze of every possible database change. It is a completed, identified capture of the state that the source collector was able to record. A later change can be recorded as a later event and will be reflected in a later snapshot. When I need to make a release decision, I can see exactly which snapshot identifiers were used instead of assuming that an unnamed “current state” will still be current tomorrow. The important boundary is therefore not an attempt to stop every change in the source database. It is the rule that a partial or failed capture must never silently become the state used for comparison. ## One source stream has one owner The normal collection runs on a schedule, but TMS also needs targeted runs for investigation and testing. Without coordination, a manual run and a scheduler run could collect the same source at the same time. Both might read the same catalog pages, and both might try to advance the same checkpoint. I did not want the second process to wait indefinitely. A delay in one source could otherwise turn into a queue of overlapping jobs. Instead, the central system keeps a short-lived lease for each source database and stream. A run either acquires the lease or records that another run already owns the work. The second run finishes with `SKIPPED_ALREADY_RUNNING`. It does not change a watermark, and it does not report a false failure. This is a small control mechanism, but it makes the behavior of the scheduler much easier to reason about: one source stream has one active collector. ## Capture locally, transfer separately Keeping the audit safe for the source environments required a second separation. ![](https://cdn.hashnode.com/uploads/covers/6461f51b60bafcc7fa959d8c/bdc413d6-2b9e-418e-8bad-f13a207a4772.png align="center") *Figure 2 - How the DEV, TEST and PROD environments connect to the central TMS.* Each environment has its own technical schema: `TMS_DEV`, `TMS_TEST` and `TMS_PROD`. When an audited DDL operation occurs, the source-side audit records it locally. The database trigger does not call the central TMS, wait for an ORDS response or make the original DDL dependent on network availability. The central system retrieves events and completed snapshots later through the existing APIs. If the central service or the network is temporarily unavailable, the source-side event or snapshot remains available for a later import. The same pattern applies to the larger captures. The source creates a snapshot header and its detail records locally, writes progress information at the start and the end of the process, and exposes completed data through paginated endpoints. The central TMS imports that data as a separate step. This separation matters most in PROD. Monitoring and auditing should provide evidence about production, not become another dependency that can interfere with it. ## A checkpoint moves only after verification Watermarks make collection incremental, but they are also a potential source of silent data loss. If a checkpoint moves as soon as a snapshot header arrives, a failure during the remaining download can cause the next run to skip details that were never imported. For that reason, the central TMS does not advance the checkpoint until it has verified the complete import. For a schema snapshot, it compares the imported counts for objects, dependencies, grants and comments with the counts in the source header. It also recalculates the manifest hash. DDL payloads are stored once per SHA-256 value to avoid needless duplication, but that storage optimization never replaces the completeness check. For an APEX snapshot, the component and change-event counts in the central database must match the source snapshot header. If the transfer stops halfway through, the checkpoint remains at the last known-good position. A later run can import the missing immutable rows, validate them again and only then advance. The result is intentionally idempotent. Repeating a successful import does not create another snapshot. Repeating an incomplete import repairs the missing details instead of declaring success because a header already exists. ![](https://cdn.hashnode.com/uploads/covers/6461f51b60bafcc7fa959d8c/8e50c712-5821-42e5-a986-c38a7991b157.png align="center") *Figure 3. A warning is not the same as a failed snapshot. The interface keeps unsupported objects and processing errors visible instead of hiding them behind a simple “same” or “different” result.* ## Scope is part of the checkpoint APEX introduced a more subtle version of the same problem. TMS can collect every configured application from a source database, but it can also collect the applications for one system only. A system-specific run must not be allowed to advance the checkpoint for the full catalog. Otherwise, a later full run could skip applications that had never been imported. The APEX snapshot checkpoint therefore includes both the source database and the collection scope: ```text (SOURCE_DATABASEID, SYNC_SCOPE) SYNC_SCOPE = ALL | SYSTEM: ``` This became a useful general rule for the project. A checkpoint is not merely a number. Its meaning includes the exact stream and scope that produced it. ## A useful audit keeps uncertainty visible I do not want the operational dashboard to compress every result into a green or red icon. The source-side progress log records the beginning and end of schema and APEX snapshot work. The central log records the overall run, each source it processes and the final outcome. That lets me distinguish a completed import from an already-running job, an unavailable expected source, a partial transfer and a real processing error. It also preserves an important distinction from Part 2: snapshot completeness is not the same as comparison coverage. A snapshot can be complete even when the current collector deliberately does not produce an independent DDL representation for a particular object type. That object should remain visible as unsupported or reviewable; it should not disappear or be treated as identical by default. ## What this makes possible The central TMS is now regularly collecting schema and APEX state from DEV, TEST and PROD. The dashboard can show snapshot history, event history, DDL differences and APEX differences together. That does not create an automatic deployment system. It creates a safer starting point for one. Once I can identify a complete source state, a complete target state and the exact snapshots used in the comparison, I can begin to assemble a reviewable release candidate. Before that, I still need one more kind of evidence: the history behind a change. In the next part, I will look at DDL events and developer context: what an audit event can tell me about an object difference, and what it cannot prove on its own.