DELETE on parent resources fails with raw PostgreSQL FK constraint error instead of cascading or returning structured error

[Sam-Bolling]

Finding

DELETE operations on parent resources that have child references (e.g., deleting a deployment that has linked systems, or a system that has datastreams) fail with a PostgreSQL foreign key constraint violation instead of cascading the delete or returning a meaningful API error.

Review Source: Live integration testing from ogc-csapi-explorer and OSHConnect-Python publishers
Severity: P1-Critical
Category: API Design / Error Handling
Ownership: connected-systems-go

---

Problem Statement

When a client sends a DELETE request for a parent resource that has foreign key references from child resources, the server returns a 500 Internal Server Error with a raw PostgreSQL foreign key violation message instead of handling the cascading relationship or returning a proper 409 Conflict response.

Affected operation:

DELETE /api/deployments/<uuid>

Expected behavior:

One of:

1. Cascade delete: remove the parent and all children (with appropriate warnings or confirmation)
2. Return 409 Conflict with a clear message listing the dependent resources that must be deleted first
3. Return 400 Bad Request explaining that the resource has active references

Actual behavior:

Returns 500 Internal Server Error with a raw PostgreSQL error:

ERROR: update or delete on table "..." violates foreign key constraint "fk_..." on table "..."

Workaround: Clients must manually delete all child resources (observations, datastreams, systems) in reverse dependency order before deleting the parent. During our testing, we had to delete resources bottom-up to avoid the constraint errors.

Impact:

• Makes cleanup and resource management difficult for API consumers
• Exposes internal database implementation details in error responses
• Forces clients to know the internal schema's FK relationships to perform deletes
• --clean / teardown operations in bootstrap scripts become fragile and order-dependent

Proposed Solutions

Option A: Cascade deletes for owned children (Recommended)

Add ON DELETE CASCADE to foreign key constraints for owned relationships (e.g., system→datastreams, datastream→observations). This matches the behavior most clients expect — deleting a system removes its datastreams and their observations.

Pros: Most intuitive behavior; simplifies client code; standard pattern for resource ownership hierarchies
Cons: Destructive — no undo; must be careful about which relationships cascade vs. restrict
Effort: Medium | Risk: Low (but must audit all FK relationships)

Option B: Return 409 Conflict with dependent resource list

Keep the FK constraints as RESTRICT, but catch the PostgreSQL error and return a proper 409 Conflict response with a body listing the dependent resources.

{
  "status": 409,
  "message": "Cannot delete deployment: 3 systems and 2 subdeployments reference this resource",
  "dependents": [
    { "type": "systems", "id": "..." },
    { "type": "deployments", "id": "..." }
  ]
}

Pros: Non-destructive; gives clients full control; explicit about what blocks the delete
Cons: More work for clients to implement cleanup; requires additional query to enumerate dependents
Effort: Medium | Risk: None

Option C: Hybrid — cascade owned, restrict referenced

• Cascade for ownership: system→datastreams→observations, deployment→subdeployments
• Restrict for references: system↔deployment links, procedure references

Return 409 for restrict-blocked deletes with a clear message.

Pros: Best of both worlds; matches OGC resource ownership semantics
Cons: More complex to implement; must define which relationships are "owned" vs "referenced"
Effort: Large | Risk: Low

Scope — What NOT to Touch

• ❌ Do NOT change the creation or update behavior for any resource type
• ❌ Do NOT expose other internal database errors in API responses

Acceptance Criteria

• Deleting a deployment with linked systems either succeeds (cascade) or returns a structured 409 error (not 500)
• Deleting a system with datastreams either succeeds (cascade) or returns a structured error
• Deleting a datastream with observations either succeeds (cascade) or returns a structured error
• No raw PostgreSQL error messages are exposed in API responses
• Delete of a resource with no dependents continues to work as before

Dependencies

Blocked by: Nothing
Blocks: Nothing

---

References

#	Document	What It Provides
1	OGC 23-001 §7.4	Resource lifecycle and delete semantics
2	OSHConnect-Python bootstrap_helpers.py	Client code that must work around the issue with ordered deletes
3	PostgreSQL FK constraint documentation	ON DELETE CASCADE vs RESTRICT options

[Sam-Bolling]

Evaluation results — 2026-04-30

Full evaluation record: docs/research/issue-evaluations/issue-002.md (commit 2658240).
Raw evidence transcripts: docs/research/evidence/issue-002/.

Methodology: static review of every r.Delete(...) route + handler + repository at HEAD 4b994212; PostgreSQL FK introspection on both production (csapi-go, port 8282) and a parallel HEAD deployment (csapi-go-head, port 8283); live empirical T1–T15 with body capture, status capture, and server-log inspection (zap stack traces + GORM SQL traces).

Verdict matrix

Claim from issue body	Verdict
DELETE returns 500 when child rows exist	✅ Confirmed (T5/T10/T11 reproduce on both HEAD and prod)
Caused by FK violation, SQLSTATE 23503	✅ Confirmed (server log: (SQLSTATE 23503))
Raw PG error is exposed in response body	❌ Refuted — body is sanitized {"error":"Failed to delete X"} (40 bytes); raw error is in zap server logs only. Quoted excerpt in issue body came from logs, not the API response.
No cascade option exists	⚠️ Partially refuted, materially confirmed — ?cascade=true IS implemented on Datastream/ControlStream/System handlers, but is empirically broken (see below)
Blocks --clean teardown of integration suites	✅ Confirmed structurally (canonical POST /systems/{id}/datastreams always creates a join row, so DELETE always collides)
FKs are on natural parent→child relations	❌ Refuted — see "Real causal model" below

Real causal model

The blocking FKs are not on observations.datastream_id or other natural parent→child columns. Those columns have no FK at all.

The 14 FKs in both DBs are exclusively on GORM-auto-generated many2many join tables:

system_datastreams        | fk_system_datastreams_datastream      | FK (datastream_id) → datastreams(id)
system_datastreams        | fk_system_datastreams_system          | FK (system_id) → systems(id)
system_controlstreams     | fk_system_controlstreams_control_stream | …
system_controlstreams     | fk_system_controlstreams_system       | …
system_deployments        | fk_system_deployments_deployment      | …
system_deployments        | fk_system_deployments_system          | …
system_procedures         | fk_system_procedures_*                | …
sampling_features         | fk_systems_sampling_features          | FK (parent_system_id) → systems(id)
systems                   | fk_systems_system_kind                | FK (system_kind_id) → procedures(id)
procedure_*_properties    | fk_procedure_*_properties_*           | …

These are emitted by GORM AutoMigrate from the gorm:"many2many:..." struct tags (internal/model/domains/datastream.go:57: Systems []System \gorm:"many2many:system_datastreams;"``). GORM defaults the join FKs to RESTRICT.

This means there are TWO bugs, not one:

1. The reported bug: Deleting a system-linked datastream/system/controlstream → 500 from the m2m join FK.
2. A silent data-integrity hazard going the other way: Deleting a datastream that has no system join but does have observations succeeds (no FK to enforce) — leaving orphaned observations with a dangling datastream_id. Same for commands.control_stream_id, system_events.system_id, system_history_revisions.system_id. Schrödinger's referential integrity.

?cascade=true is also broken (new finding)

internal/repository/datastream_repository.go:134-146:

return r.db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Where("datastream_id = ?", id).Delete(&domains.Observation{}).Error; err != nil {
        return err
    }
    return tx.Delete(&domains.Datastream{}, "id = ?", id).Error  // ← still trips fk_system_datastreams_datastream
})

GORM's plain db.Delete(&Datastream{}, …) does not propagate to many2many join rows. Empirical confirmation:

Test	Request	Status	Server log
T5	DELETE /datastreams/{id}	500	fk_system_datastreams_datastream
T8	DELETE /datastreams/{id}?cascade=true	500	fk_system_datastreams_datastream
T10	DELETE /systems/{id}	500	fk_system_datastreams_system
T11	DELETE /systems/{id}?cascade=true	500	fk_system_datastreams_system (raised at system_repository.go:186)
T13	DELETE /deployments/{id} (no joins)	204	—
T15	DELETE /procedures/{id} (no joins)	204	—

SystemRepository.deleteCascade does explicitly clean system_deployments and system_procedures join tables, but forgets system_datastreams and system_controlstreams — which is exactly why T11 still 500s.

DeploymentRepository.Delete(id string) has no cascade parameter at all — there is no escape hatch for deployments linked to systems.

Recommended remediation (three coordinated fixes)

1. Repository layer — make cascade actually cascade. Explicitly clear m2m join rows before deleting the resource. Mirror the existing system_deployments/system_procedures pattern in SystemRepository.deleteCascade:

   if err := tx.Exec("DELETE FROM system_datastreams WHERE datastream_id = ?", id).Error; err != nil { return err }
   return tx.Delete(&domains.Datastream{}, "id = ?", id).Error

   Apply to DatastreamRepository.Delete, ControlStreamRepository.Delete, both directions in SystemRepository.deleteCascade, and add a cascade param to DeploymentRepository.Delete.

2. Handler layer — map error classes to HTTP codes. Detect *pgconn.PgError with code 23503 → 409 Conflict with a generic message ("Resource has dependent rows; use ?cascade=true or remove dependents first"). Detect gorm.ErrRecordNotFound → 404. Keeps body sanitized (no SQL leak) but gives clients an actionable status.

3. Close the silent-orphaning gap. Either add proper gorm:"foreignKey:…;constraint:OnDelete:RESTRICT" tags to Observation.DatastreamID, Command.ControlStreamID, SystemEvent.SystemID, SystemHistoryRevision.SystemID; or have the cascade impls explicitly delete those children even without an FK. The current state (no FK + no cascade-cleanup) is the worst of both worlds.

Suggested issue-body edits (for maintainer reference)

• Replace "raw PostgreSQL error is exposed in API response" with "raw PostgreSQL error is logged server-side; API response is a generic 500 with no actionable diagnosis"
• Replace "no ?cascade=true option" with "?cascade=true exists on 3 of 12 DELETE handlers but is non-functional for the canonical case"
• Add note that the underlying FKs are on the GORM-generated m2m join tables, not on declared parent→child relations

Follow-up surfaces (separate issues)

I'll file an umbrella follow-up covering the cascade-impl breakage and the silent-orphaning hazard separately, linking back here.

cc / context: the parallel HEAD deployment used for empirical verification is at https://129-80-248-53.sslip.io/csapi-go-head/ (cs-go HEAD 4b99421); production is at https://129-80-248-53.sslip.io/csapi-go/ (pre-1562201). Both have byte-identical FK schemas.

[Sam-Bolling]

Closing as resolved upstream. Posting full evidence so the closure is auditable.

Resolution

The defect this issue describes — DELETE on a parent resource with related child rows returning HTTP 500 with the raw FK violation surfaced in the body, and no working ?cascade=true semantics — is comprehensively fixed in upstream commit fe9fbd0 ("Adding cascade delete and fixing existing cascade delete to full delete", 2026-05-01), now in this fork via merge 22e8bcd.

Maintainer's read

@SomethingCreativeStudios on the upstream triage pass: "DELETE 404, 409 for cascade" (#17), "Cascade DELETE fails for all related resource types (mostly a dup of issue 2) same fix" (#16), and on this issue itself: "Junction table fixed and fk issue resolved." — i.e. issues #2, #16, #17 were all collapsed onto this single commit.

What changed

fe9fbd0 introduces a small error-classification layer and propagates it through every DELETE handler. Three concrete pieces:

1. New typed errors at internal/repository/errors.go:

var ErrNotFound      = errors.New("record not found")
var ErrHasChildren   = errors.New("resource has dependent records")

func isFKViolation(err error) bool {
    var pgErr *pgconn.PgError
    return errors.As(err, &pgErr) && pgErr.Code == "23503"
}

2. Error-class discrimination in every DELETE handler — replacing the old generic err != nil → 500 + "Failed to delete X" shape. The new pattern (representative, DatastreamHandler.DeleteDatastream):

if err := h.repo.Delete(id, cascade); err != nil {
    switch {
    case errors.Is(err, repository.ErrNotFound):
        render.Status(r, http.StatusNotFound)
        render.JSON(w, r, map[string]string{"error": "Datastream not found"})
    case errors.Is(err, repository.ErrHasChildren):
        render.Status(r, http.StatusConflict)
        render.JSON(w, r, map[string]string{"error": "Datastream has dependent records; use ?cascade=true to delete"})
    default:
        h.logger.Error(...); render.Status(r, http.StatusInternalServerError); ...
    }
}

This pattern is replicated across all 12 DELETE handlers (command, control_stream, datastream, deployment, feature, observation, procedure, property, sampling_feature, system, plus the two implicit ones touched here). The raw PostgreSQL error string is no longer reachable via the API surface — addressing C3 from the issue body.

3. Cascade actually works. The repository-layer Delete(id, cascade) was rewritten to do an ordered teardown of dependent rows (including the m2m join tables that this fork's evaluation singled out as the actual FK source — see fk-constraints-head-2026-04-30.txt). New end-to-end coverage at e2e/delete_test.go (+140 LOC) and an expanded internal/repository/cascade_delete_repository_test.go (+167/-) cover the cases.

Mapping fix → original claims

Claim from issue body	Fixed how
C1 — DELETE returns 500 on related rows	No: now returns 409 Conflict + ErrHasChildren when cascade not requested
C2 — Cause is FK violation (SQLSTATE 23503)	Detection added via isFKViolation (pgconn.PgError code check)
C3 — Raw PG error in body	Eliminated: bodies are now fixed strings classified by error type
C4 — No working cascade option	?cascade=true now reliably tears down dependent rows in the correct order, including m2m joins (per fe9fbd0 + cascade_delete_repository_test.go)
C5 — Blocks --clean teardown in OSHConnect / ogc-csapi-explorer	Should be unblocked once those tools are pointed at current main
C6 — FK origin claim	The original evaluation (§2.3) found FKs originate from the GORM-generated m2m join tables, not from natural parent→child columns. The fix correctly addresses the m2m tables via ordered cascade teardown rather than by adding new FKs.

Verification

Against current main (post-merge 22e8bcd):

• internal/repository/errors.go exists with ErrNotFound, ErrHasChildren, isFKViolation as quoted above.
• All 12 DELETE handlers in internal/api/*_handler.go show the errors.Is(...) switch pattern, no longer the bare err != nil → 500 shape.
• e2e/delete_test.go exists and exercises the 200/404/409 surface plus cascade success paths.

Cross-references

• Original evaluation: docs/research/issue-evaluations/issue-002.md (15 live tests T1–T15, FK introspection on both prod and HEAD DBs, handler-by-handler static analysis).
• Issues collapsed onto the same fix: [bug] DELETE cascade is broken across resource types — m2m join FKs not cleaned, and natural parent→child relations have no FK at all #16 (cascade fails for all related resource types), [enhancement] All 12 DELETE handlers map every repository error to HTTP 500 — should distinguish 404 (not found), 409 (FK conflict / unique violation), 400 (validation), 500 (true internal) #17 (DELETE 404 + 409 for cascade).

Closing as completed.