fluent psql updated constraint is not accessible

[wibed]

i do experience an issue on updating a constraint on a foreign table.

to circumvent circular dependencies on migration i create the table without the foreign key and add it in on a later moment.

the fk in question is the one from contract.schema to account.schema, where in contract.schema the field "author_id" references to the "id" field on account.schema.

i update the field as follows:

try await database.schema(Contract.schema)
.foreignKey("author_id", references: Account.schema, "id", onDelete: .cascade)
.update()

the sql command is correct aswell:

ALTER TABLE "contract" ADD CONSTRAINT "fk:contract.author_id+contract.id" FOREIGN KEY ("author_id") REFERENCES "account" ("id") ON DELETE CASCADE ON UPDATE NO ACTION

the foreign key does exist: (see pic one) https://dataedo.com/kb/query/postgresql/list-foreign-keys

the table aswell has an entry, (i do create the table entry not attach it)

in lldb i get mixed messages

(lldb) p try contractList[2].joined(Account.self).$id.value
(Foundation.UUID?) nil

vs

p contractList[2].$author.id
(Foundation.UUID?) 2165A1E4-7928-4571-9124-A46B0C0A30D1

the table definition

extension Contract {
  struct Migration: Fluent.AsyncMigration {
    var name: String { "CreateContract" }

    func prepare(on database: Database) async throws {
      try await database.schema(Contract.schema)
        .id()
        .field("author_id", .uuid)
        .field("name", .string, .required)
...
        .field("created_at", .double, .required)
        .field("updated_at", .double, .required)
        .field("deleted_at", .double)
        .unique(on: "name", "deleted_at" )
        .create()
    }

    func revert(on database: Database) async throws {
      try await database.schema(Contract.schema).delete()
    }
  }
}

the function in question

  func searchContract( database: Database? = nil ) async throws -> [Contract.contractPublic] {
    let database = database ?? self.database

    let query: QueryBuilder<Contract> = try await _search(database: database)
    let result = try await query.all()
    let contractList = result

    return try contractList.compactMap { contract in

      let contractAuthor = try contract.joined(Account.self)
      guard let contractAuthorID = contractAuthor.id else { return nil }

      let contractID = try contract.id.value(or: Abort(.notFound))
...

      return Contract.contractPublic(
        id: contractID,
        authorID: contractAuthorID,
        name: contractName,
...
      )
    }
  }

[wibed]

missing join statement on _search was the reason