Checkpoint Lab — Database, SSH, Filesystem, Process, and Infrastructure Automation Patterns

1. Safety charter

• The only database is results/.../scratch-rf18/inventory.sqlite3.
• The only recursive delete target is the exact scratch directory created by this run.
• The only child program is the current Python interpreter with a constant -c snippet.
• No real SSH host is required; the SSH contract is simulated unless a separately governed local fixture already exists.
• No passwords, private keys, public hosts, production services, privileged commands, or cloud resources are used.
• Original failure artifacts are retained before any repaired rerun.

2. Exact preflight and assumptions

python --version
python -m robot --version
python -m pip show robotframework-databaselibrary
python -c "import sqlite3,sys; print(sys.version); print(sqlite3.sqlite_version)"
# Expected pins for this chapter:
# Robot Framework 7.4.2
# DatabaseLibrary 2.4.1
# SSHLibrary not required for mandatory path

Record the exact values in your evidence packet. If results/checkpoint/scratch-rf18 already exists, stop and inspect it. The suite is designed to fail setup instead of deleting a pre-existing directory.

3. Project files

rf18-infra-lab/
├── resources/
│   └── infra.resource
├── tests/
│   └── infrastructure.robot
└── results/
    └── <run-specific output>/
        └── scratch-rf18/
            ├── inventory.sqlite3
            └── marker.txt

Use the infra.resource from Lesson 2. Place the checkpoint test file below in tests/infrastructure.robot.

4. Predict state changes before execution

5. Checkpoint suite

*** Settings ***
Resource         ../resources/infra.resource
Suite Setup      Prepare Owned Infrastructure Fixture
Suite Teardown   Guarded Cleanup

*** Test Cases ***
Checkpoint State Matrix
    # Predict first: DB gains one committed row, marker is created,
    # process returns rc=0, simulated SSH changes no external state.
    Insert Inventory Row    checkpoint-alpha    ready
    ${rows}=    Get Inventory Row    checkpoint-alpha
    Length Should Be    ${rows}    1
    Should Be Equal    ${rows}[0][state]    ready

    ${marker}=    Write Owned Marker    checkpoint=rf18\nowner=current-run\n
    ${marker_text}=    Get File    ${marker}    encoding=UTF-8
    Should Contain    ${marker_text}    owner=current-run

    ${process}=    Run Harmless Child
    Should Be Equal As Integers    ${process.rc}    0
    Should Contain    ${process.stdout}    rf18

    ${ssh_sim}=    Simulate Read Only SSH Contract
    Should Contain    ${ssh_sim.stdout}    "rc": 0

Cleanup Guard Rejects Escaped Root
    ${safe}=    Normalize Path    ${OUTPUTDIR}${/}${SCRATCH_NAME}
    ${escaped}=    Normalize Path    ${OUTPUTDIR}${/}${SCRATCH_NAME}${/}..${/}outside
    ${status}=    Run Keyword And Return Status    Should Be Equal    ${escaped}    ${safe}
    Should Be Equal    ${status}    ${False}
    Directory Should Exist    ${LAB_ROOT}

Parameterized Query Treats Payload As Data
    Insert Inventory Row    literal-' OR 1=1 --    ready
    ${rows}=    Get Inventory Row    literal-' OR 1=1 --
    Length Should Be    ${rows}    1
    Should Be Equal    ${rows}[0][name]    literal-' OR 1=1 --

The second test intentionally asks the guard to compare an escaped path to the allowed root using a status-returning assertion. The expected result is False; no delete operation follows. This proves the guard detects path escape without performing the dangerous action.

6. Run the checkpoint

# PowerShell
python -m robot --outputdir results\checkpoint tests\infrastructure.robot

# Bash / POSIX
python -m robot --outputdir results/checkpoint tests/infrastructure.robot

Expected high-level result: all checkpoint tests pass, because the path-escape case explicitly verifies the guard rejects the unsafe candidate. After suite teardown, scratch-rf18 is absent but the Robot result files remain.

7. Failure injection A: pre-existing scratch root

Create results/failure-owned/scratch-rf18 manually with a sentinel file, then run the suite with --outputdir results/failure-owned. Predict: suite setup fails before claiming ownership; suite teardown does not recursively delete the directory because ${OWNED_ROOT} remains false. Preserve both the Robot failure and sentinel.

# Example shell-neutral intent (use your platform's normal directory/file commands):
# 1. create results/failure-owned/scratch-rf18
# 2. add sentinel.txt
python -m robot --outputdir results/failure-owned tests/infrastructure.robot
# 3. verify sentinel.txt still exists

The repair is to choose a fresh result directory or investigate the stale owner. Do not modify setup to delete first.

8. Failure injection B: wrong database expectation

Change the expected state for checkpoint-alpha from ready to blocked and run only the first checkpoint test into results/failure-db. The database operation succeeds; the business/storage assertion fails. Teardown still removes the owned scratch root. Restore the expectation and rerun into results/repaired-db, preserving both result directories.

9. Optional transaction proof

Add the Lesson 2 Uncommitted Insert Is Not Durable After Disconnect test. Before execution, predict whether your recorded Python/sqlite3 configuration will persist the pending row. Use DatabaseLibrary's no_transaction=True only in that controlled probe. The evidence requirement is the before-disconnect query, reconnect, after-reconnect query, and version information—not an assumed outcome detached from the driver configuration.

10. Optional real SSH path — only with an already safe fixture

If you already have a disposable localhost/container SSH service, do not simply copy a password example and call it secure. Record host/port, account privilege, host key fingerprint, how the client rejects unknown/changed host keys, command allowlist, connection lifetime, and cleanup. Because SSHLibrary 3.8.0's stable Paramiko implementation uses AutoAddPolicy, this course does not make that path mandatory or present it as a production trust baseline.

A documented simulation is preferable to a misleading “secure” demo.

11. Required evidence packet

• Python, Robot Framework, DatabaseLibrary, SQLite versions; SSHLibrary version only if optional path is installed.
• Exact Robot command and output directory.
• Resolved normalized scratch root and DB file path.
• Proof scratch did not exist before setup and was created/owned by the run.
• Schema/query evidence and returned committed row.
• Injection-looking literal value stored/retrieved safely through parameters.
• Process program identity, rc, stdout, stderr.
• SSH simulation output or, for optional real SSH, trust/target provenance.
• Path-escape guard result.
• Original injected-failure output.xml/log and repaired run kept separately.
• Post-teardown proof scratch root is gone while result artifacts remain.

12. Verification checklist

• No database path escapes the exact run-owned root.
• No SQL data is concatenated into executable SQL.
• No shell is enabled for the mandatory Process calls.
• No process is killed by broad name/pattern.
• No SSH host-key/TLS verification is disabled.
• No real credential, production host/database, or privileged command appears.
• No broad EXCEPT, blanket retry, giant sleep, or arbitrary PYTHONPATH masks a failure.
• Failure and repaired artifacts are separate.
• Cleanup is ownership-aware and exact-path guarded.

13. Cleanup and rollback

1. Let suite teardown disconnect the DatabaseLibrary connection.
2. Let the exact-root guard validate the scratch path.
3. Delete the root only when the run-owned flag is true.
4. Verify scratch no longer exists.
5. Keep Robot artifacts until review is complete.
6. Remove only deliberately created manual failure-injection directories after verifying their sentinel/ownership.

14. What Chapter 18 adds to the production operating model

You can now treat infrastructure-facing automation as governed integration evidence: target allowlist first, high-level resource keyword second, explicit database/SSH/filesystem/process state owners, parameterized data, bounded execution, independent verification, first-failure retention, and cleanup that proves ownership before mutation.

Chapter 19 moves from short-lived tests toward RPA tasks and long-running automation. That introduces durable workflow state, resumability, human approval, and compensation—concerns that become manageable only because external-state ownership is already explicit.

Knowledge check

References and version anchors

• Robot Framework 7.4.2 User Guide, Process 7.4.2, and OperatingSystem 7.4.2 — core execution, variables/lifecycle, structured child processes, filesystem operations, and current standard-library semantics.
• DatabaseLibrary 2.4.1 keyword documentation, PyPI release, and maintainer repository — connection, query, transaction wrapper, parameter, retry, alias, and SQLite guidance.
• SSHLibrary keyword documentation, SSHLibrary 3.8.0 on PyPI, and maintainer repository — connection/session behavior and current release status. The mandatory lab does not rely on a real SSH server.
• Python sqlite3 documentation — SQLite connection/transaction behavior used by the local database fixture.