PostgreSQL Updates

#Explicit Opt-In

PostgreSQL attachment grants reads and zero update scopes. An operator must separately enable exact current tables and assignment columns before the update tools become applicable.

Daita supports one structured operation: a parameterized PostgreSQL UPDATE over one current base table, one or more AND-combined filters, and one or more literal assignments. The model never writes the SQL statement.

Inserts, deletes, arbitrary SQL, DDL, stored procedures, role or grant administration, SQLite writes, and local dataset mutation are unsupported.

#Four Required Controls

Every update requires all four boundaries:

1. The attached PostgreSQL role has the exact native privileges.
2. Daita source permissions enable the exact table and assignment columns.
3. Current non-mutating readiness validates the table, role, grants, and catalog.
4. The user approves the exact preview once.

The model cannot grant database privileges, change source permissions, or approve its own operation.

Use a dedicated least-privileged role with CONNECT, schema USAGE, table SELECT, and column-level UPDATE only where needed. Daita rejects powerful roles, row-level security, user triggers, custom rewrite rules, ineligible table kinds, unsupported primary keys, and unsafe assignment columns.

#Enable Exact Scopes

The terminal flow is:

/source permissions

Choose the PostgreSQL source, select exact current tables, select all eligible assignment columns or an Advanced subset, review the before/after summary, and confirm.

Python callers use the same preview-and-apply control plane:

inspection = await agent.inspect_source_permissions(source.id)
 
preview = await agent.preview_source_permissions(
    source_id=source.id,
    read_mode="all",
    read_resource_ids=(),
    postgresql_update_scopes={
        tickets_resource_id: ("priority",),
    },
)
 
# Show preview.summary to the operator before this explicit apply.
permissions = await agent.apply_source_permissions(
    source_id=source.id,
    confirmation_fingerprint=preview.confirmation_fingerprint,
)

The permission preview expires on catalog or permission drift. Future tables are never automatically write-enabled. Editing a PostgreSQL connection clears every update scope.

#Check Readiness

Readiness is non-mutating:

readiness = await agent.postgresql_update_readiness(
    source.id,
    tickets_resource_id,
    ("priority",),
)

or:

daita postgresql-update-readiness atlas <source-id> <resource-id> \
  --assignment-column priority

Apply any database-role or grant remediation outside Daita through the normal DBA process.

#Preview and Approve

Ask for the exact intended change in plain language:

Set priority to high for support tickets where ticket_status is waiting and category is billing.

data_preview_postgresql_update identifies the complete matching primary-key set, exact matched row count, target fingerprint, and up to five bounded before/after samples. There is no product row-count ceiling, so every preview may represent a high-impact operation.

Verify the source, table, every filter, every assigned value, the exact row count, and the samples. data_update_postgresql then opens the approval interaction for that exact preview. The approval cannot be reused with different arguments, after drift, or in a later run.

#Transaction and Drift

After approval, Daita opens one transaction, locks the target rows, and rechecks the live catalog, table guardrails, source permissions, PostgreSQL privileges, row count, complete primary-key set, and current assigned values.

Any drift rolls back before the update and requires a fresh preview. Otherwise Daita executes the parameterized statement once and requires the affected-row count to match the approved expectation. It does not automatically retry an uncertain outcome.

#Receipts and Outcomes

An immutable local receipt records the exact run and call identity, arguments and target fingerprints, expected row count, terminal outcome, and affected count when known.

outcome_unknown does not mean zero rows changed. Remove the Daita update scope, preserve the receipt ID, do not retry, read the target rows through an independent operation, and review PostgreSQL evidence with the authorized DBA. A later observation never rewrites the immutable receipt.

For non-disposable data, verify backup retention, point-in-time recovery, restore ownership, and a recent restore exercise before enabling updates. A Daita receipt is evidence, not a backup or recovery mechanism.