### On Delete Behavior Examples

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/references.md

Illustrates different `on_delete` strategies for foreign key constraints, including `:nothing`, `:restrict`, `:delete`, and `:nilify`.

```elixir
references do
  reference :post, on_delete: :nothing
  # vs
  reference :post, on_delete: :restrict
end
```

--------------------------------

### Install Ash Postgres with Igniter

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Use the Igniter mix task to install the ash_postgres dependency.

```bash
mix igniter.install ash_postgres
```

--------------------------------

### List Partitioning Setup

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/partitioned-tables.md

Configure a table for list partitioning by region using `create_table_options`. A default partition is created to catch any data that doesn't fit into specific partitions.

```elixir
defmodule MyApp.Order do
  use Ash.Resource,
    domain: MyApp.Domain,
    data_layer: AshPostgres.DataLayer

  attributes do
    uuid_primary_key :id
    attribute :order_number, :string
    attribute :region, :string
    attribute :total, :decimal
    create_timestamp :inserted_at
  end

  postgres do
    table "orders"
    repo MyApp.Repo

    # Configure the table as a list-partitioned table
    create_table_options "PARTITION BY LIST (region)"

    # Create a default partition
    custom_statements do
      statement :default_partition do
        up """
        CREATE TABLE IF NOT EXISTS orders_default
        PARTITION OF orders DEFAULT;
        """
        down """
        DROP TABLE IF EXISTS orders_default;
        """
      end
    end
  end
end
```

--------------------------------

### Setup Database and Apply Migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Set up your local database and apply the generated migrations using the Ash CLI. This command ensures your database schema is up-to-date.

```bash
mix ash.setup
```

--------------------------------

### Add Repo to Application Supervision Tree

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Include your configured Helpdesk.Repo in the children list of your application's start function to ensure it is supervised.

```elixir
# in lib/helpdesk/application.ex

  def start(_type, _args) do
    children = [
      # Starts a worker by calling: Helpdesk.Worker.start_link(arg)
      # {Helpdesk.Worker, arg}
      Helpdesk.Repo
    ]

    ...

```

--------------------------------

### Range Partitioning Setup

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/partitioned-tables.md

Configure a table for range partitioning by date using `create_table_options`. A default partition is created to catch any data that doesn't fit into specific partitions.

```elixir
defmodule MyApp.SensorReading do
  use Ash.Resource,
    domain: MyApp.Domain,
    data_layer: AshPostgres.DataLayer

  attributes do
    uuid_primary_key :id
    attribute :sensor_id, :integer
    attribute :reading_value, :float
    create_timestamp :inserted_at
  end

  postgres do
    table "sensor_readings"
    repo MyApp.Repo

    # Configure the table as a partitioned table
    create_table_options "PARTITION BY RANGE (inserted_at)"

    # Create a default partition to catch any data that doesn't fit into specific partitions
    custom_statements do
      statement :default_partition do
        up """
        CREATE TABLE IF NOT EXISTS sensor_readings_default
        PARTITION OF sensor_readings DEFAULT;
        """
        down """
        DROP TABLE IF EXISTS sensor_readings_default;
        """
      end
    end
  end
end
```

--------------------------------

### Hash Partitioning Setup

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/partitioned-tables.md

Configure a table for hash partitioning by user ID using `create_table_options`. A default partition is created to catch any data that doesn't fit into specific partitions.

```elixir
defmodule MyApp.LogEntry do
  use Ash.Resource,
    domain: MyApp.Domain,
    data_layer: AshPostgres.DataLayer

  attributes do
    uuid_primary_key :id
    attribute :user_id, :integer
    attribute :message, :string
    create_timestamp :inserted_at
  end

  postgres do
    table "log_entries"
    repo MyApp.Repo

    # Configure the table as a hash-partitioned table
    create_table_options "PARTITION BY HASH (user_id)"

    # Create a default partition
    custom_statements do
      statement :default_partition do
        up """
        CREATE TABLE IF NOT EXISTS log_entries_default
        PARTITION OF log_entries DEFAULT;
        """
        down """
        DROP TABLE IF EXISTS log_entries_default;
        """
      end
    end
  end
end
```

--------------------------------

### Create New Application with Ash and AshPostgres

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/set-up-with-existing-database.md

Use this command to create a new Elixir application and install the Ash and AshPostgres libraries. Add `--with phx.new` if you plan to use Phoenix.

```bash
mix igniter.new my_app \
  --install ash,ash_postgres \
  --with phx.new # add this if you will be using phoenix too
```

--------------------------------

### Ash Resource File Configuration

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/working-with-existing-databases.md

This is an example of a customized Ash resource file. It includes `migrate? false` in the `postgres` block to prevent migration generation.

```elixir
defmodule MyApp.ExternalDb.User do
  use Ash.Resource,
    domain: MyApp.ExternalDb,
    data_layer: AshPostgres.DataLayer,
    fragments: [MyApp.ExternalDb.User.Model]

  postgres do
    table "users"
    repo MyApp.Repo
    migrate? false
  end
end
```

--------------------------------

### Configure Postgres References

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/references.md

Example of configuring foreign key references within the `postgres` block. This sets up deletion and update behaviors for related data.

```elixir
postgres do
  # other PostgreSQL config here

  references do
    reference :post, on_delete: :delete, on_update: :update, name: "comments_to_posts_fkey"
  end
end
```

--------------------------------

### Customizing Resource with Actions and Calculations

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/working-with-existing-databases.md

Example of adding custom actions and calculations to an Ash resource file. These customizations are preserved during fragment regeneration.

```elixir
defmodule MyApp.ExternalDb.User do
  use Ash.Resource,
    domain: MyApp.ExternalDb,
    data_layer: AshPostgres.DataLayer,
    fragments: [MyApp.ExternalDb.User.Model]

  actions do
    defaults [:read]

    read :by_email do
      argument :email, :string, allow_nil?: false
      filter expr(email == ^arg(:email))
    end
  end

  calculations do
    calculate :display_name, :string, expr(name || email)
  end

  postgres do
    table "users"
    repo MyApp.Repo
    migrate? false
  end
end
```

--------------------------------

### Configure Automatic Tenant Management with `manage_tenant`

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/schema-based-multitenancy.md

Use the `manage_tenant` directive within the `postgres` block to automatically create and manage database schemas based on resource attributes. This example uses the organization's ID to create schemas like `org_10`.

```elixir
defmodule MyApp.Organization do
  use Ash.Resource,
    ...

  postgres do
    ...

    manage_tenant do
      template ["org_", :id]
    end
  end
end
```

--------------------------------

### Elixir Test DataCase Setup

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/development/testing.md

Use this module template in your tests to ensure all database interactions run within a transaction. It enables Ecto's SQL sandbox and provides necessary aliases and imports for testing.

```elixir
defmodule MyApp.DataCase do
  @moduledoc """
  This module defines the setup for tests requiring
  access to the application's data layer.

  You may define functions here to be used as helpers in
  your tests.

  Finally, if the test case interacts with the database,
  we enable the SQL sandbox, so changes done to the database
  are reverted at the end of every test. If you are using
  PostgreSQL, you can even run database tests asynchronously
  by setting `use AshHq.DataCase, async: true`, although
  this option is not recommended for other databases.
  """

  use ExUnit.CaseTemplate

  using do
    quote do
      alias MyApp.Repo

      import Ecto
      import Ecto.Changeset
      import Ecto.Query
      import MyApp.DataCase
    end
  end

  setup tags do
    pid = Ecto.Adapters.SQL.Sandbox.start_owner!(MyApp.Repo, shared: not tags[:async])
    on_exit(fn -> Ecto.Adapters.SQL.Sandbox.stop_owner(pid) end)
    :ok
  end
end
```

--------------------------------

### Create and Update Data with AshPostgres

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Create and update data in your AshPostgres resources using Ash's changeset API. This example demonstrates creating a representative and several tickets, then assigning and updating them.

```elixir
require Ash.Query

representative = (
  Helpdesk.Support.Representative
  |> Ash.Changeset.for_create(:create, %{name: "Joe Armstrong"})
  |> Ash.create!()
)

for i <- 0..5 do
  ticket = 
    Helpdesk.Support.Ticket
    |> Ash.Changeset.for_create(:open, %{subject: "Issue #{i}"})
    |> Ash.create!()
    |> Ash.Changeset.for_update(:assign, %{representative_id: representative.id})
    |> Ash.update!()

  if rem(i, 2) == 0 do
    ticket
    |> Ash.Changeset.for_update(:close)
    |> Ash.update!()
  end
end
```

--------------------------------

### Create Configuration Files

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Generate the necessary configuration files for different environments.

```bash
mkdir -p config
touch config/config.exs
touch config/dev.exs
touch config/runtime.exs
touch config/test.exs
```

--------------------------------

### Filter using `trigram_similarity`

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/expressions.md

Filter records based on the trigram similarity of text fields. This requires the `pg_trgm` extension to be installed and uses the `similarity` function from PostgreSQL.

```elixir
Ash.Query.filter(User, trigram_similarity(first_name, "fred") > 0.8)
```

--------------------------------

### Generated Migration for UUIDv7 Default

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/development/upgrading-to-postgres-18.md

Example of migrations generated by `ash.codegen` to update the default value for a UUID column to use the database's `uuidv7()` function.

```elixir
  def up do
    alter table(:items) do
      modify(:id, :uuid, default: fragment("uuidv7()"))
    end
  end

  def down do
    alter table(:items) do
      modify(:id, :uuid, default: fragment("uuid_generate_v7()"))
    end
  end
```

--------------------------------

### Configure Base Application Settings

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Set up the base configuration for your application, including Ash domains and Ecto repositories. Ensure environment-specific configurations are imported at the end.

```elixir
# in config/config.exs
import Config

# This should already have been added in the first
# getting started guide
config :helpdesk,
  ash_domains: [Helpdesk.Support]

config :helpdesk,
  ecto_repos: [Helpdesk.Repo]

# Import environment specific config. This must remain at the bottom
# of this file so it overrides the configuration defined above.
import_config "#{config_env()}.exs"
```

--------------------------------

### Generate Resources for Multiple Domains

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/set-up-with-existing-database.md

Allows for more fine-grained control by separating application resources into multiple domains. Run the command for each domain and its associated tables.

```bash
mix ash_postgres.gen.resources MyApp.Accounts --tables users,roles,tokens
```

```bash
mix ash_postgres.gen.resources MyApp.Blog --tables posts,comments
```

--------------------------------

### Dynamically Create List Partitions

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/partitioned-tables.md

A helper function to dynamically create partitions for list-partitioned tables as part of an action. Handles duplicate table errors gracefully.

```elixir
def create_partition(resource, partition_name, list_value) do
  repo = AshPostgres.DataLayer.Info.repo(resource)
  table_name = AshPostgres.DataLayer.Info.table(resource)
  schema = AshPostgres.DataLayer.Info.schema(resource) || "public"

  sql = """
  CREATE TABLE IF NOT EXISTS \"#{schema}\".\"#{partition_name}\"\n  PARTITION OF \"#{schema}\".\"#{table_name}\"\n  FOR VALUES IN ('#{list_value}')
  """

  case Ecto.Adapters.SQL.query(repo, sql, []) do
    {:ok, _} ->
      :ok

    {:error, %{postgres: %{code: :duplicate_table}}} ->
      :ok

    {:error, error} ->
      {:error, "Failed to create partition for #{table_name}: #{inspect(error)}"}
  end
end
```

--------------------------------

### Query Data with AshPostgres (Complex Filter)

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Perform more complex queries on your AshPostgres data. This example filters tickets that are closed and whose subject does not contain a specific substring.

```elixir
require Ash.Query

# Show the tickets that are closed and their subject does not contain "4"
Helpdesk.Support.Ticket
|> Ash.Query.filter(status == :closed and not(contains(subject, "4")))
|> Ash.read!()
```

--------------------------------

### Configure Test Database

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Set up the database connection details for the test environment. The database name can be dynamically suffixed with the MIX_TEST_PARTITION environment variable for CI partitioning.

```elixir
# in config/test.exs

import Config

# Configure your database
#
# The MIX_TEST_PARTITION environment variable can be used
# to provide built-in test partitioning in CI environment.
# Run `mix help test` for more information.
config :helpdesk, Helpdesk.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "helpdesk_test#{System.get_env("MIX_TEST_PARTITION")}",
  pool: Ecto.Adapters.SQL.Sandbox,
  pool_size: 10
```

--------------------------------

### Configure Development Database

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Set the database connection details for the development environment. Adjust username, password, hostname, database name, and port as needed.

```elixir
# in config/dev.exs

import Config

# Configure your database
config :helpdesk, Helpdesk.Repo,
  username: "postgres",
  password: "postgres",
  hostname: "localhost",
  database: "helpdesk_dev",
  port: 5432,
  show_sensitive_data_on_connection_error: true,
  pool_size: 10
```

--------------------------------

### Query Data with AshPostgres (Filter by Subject)

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Read data from your AshPostgres resources using Ash's query API. This example filters tickets based on a substring in their subject.

```elixir
require Ash.Query

# Show the tickets where the subject contains "2"
Helpdesk.Support.Ticket
|> Ash.Query.filter(contains(subject, "2"))
|> Ash.read!()
```

--------------------------------

### Configure Postgres Data Layer

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/dsls/DSL-AshPostgres.DataLayer.md

Sets up the PostgreSQL data layer, specifying the Ecto repository and the primary table name for the resource.

```elixir
postgres do
  repo MyApp.Repo
  table "organizations"
end
```

--------------------------------

### Use fragments in migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/expressions.md

Utilize PostgreSQL fragments within Ash migrations to define default values or constraints that rely on database functions. This example sets a default UUID for a primary key.

```elixir
create table(:managers, primary_key: false) do
  add :id, :uuid, null: false, default: fragment("UUID_GENERATE_V4()"), primary_key: true
end
```

--------------------------------

### Create Additional Range Partitions

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/partitioned-tables.md

Use custom statements to create monthly partitions for a range-partitioned table. Includes statements for a default partition and specific monthly partitions.

```elixir
postgres do
  table "sensor_readings"
  repo MyApp.Repo

  create_table_options "PARTITION BY RANGE (inserted_at)"

  custom_statements do
    statement :default_partition do
      up """
      CREATE TABLE IF NOT EXISTS sensor_readings_default
      PARTITION OF sensor_readings DEFAULT;
      """
      down """
      DROP TABLE IF EXISTS sensor_readings_default;
      """
    end

    # Example: Create a partition for January 2024
    statement :january_2024_partition do
      up """
      CREATE TABLE IF NOT EXISTS sensor_readings_2024_01
      PARTITION OF sensor_readings
      FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
      """
      down """
      DROP TABLE IF EXISTS sensor_readings_2024_01;
      """
    end

    # Example: Create a partition for February 2024
    statement :february_2024_partition do
      up """
      CREATE TABLE IF NOT EXISTS sensor_readings_2024_02
      PARTITION OF sensor_readings
      FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
      """
      down """
      DROP TABLE IF EXISTS sensor_readings_2024_02;
      """
    end
  end
end
```

--------------------------------

### Configure Formatter for Ash Postgres

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Include :ash_postgres in your .formatter.exs to ensure proper code formatting.

```elixir
[
  # import the formatter rules from `:ash_postgres`
  import_deps: [..., :ash_postgres],
  inputs: [...]
]
```

--------------------------------

### Configure Production Database URL

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

For production environments, configure the database connection using the DATABASE_URL environment variable. The pool size can also be configured via the POOL_SIZE environment variable.

```elixir
# in config/runtime.exs

import Config

if config_env() == :prod do
  database_url = 
    System.get_env("DATABASE_URL") ||
      raise """
      environment variable DATABASE_URL is missing.
      For example: ecto://USER:PASS@HOST/DATABASE
      """

  config :helpdesk, Helpdesk.Repo,
    url: database_url,
    pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")
end
```

--------------------------------

### Run Tenant Migrations with AshPostgres

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/multitenancy.md

Execute tenant-specific database migrations using the `mix ash_postgres.migrate --tenants` command. This command is run in addition to regular migrations.

```bash
# Run regular migrations
mix ash.migrate

# Run tenant migrations
mix ash_postgres.migrate --tenants
```

--------------------------------

### Configure Tenant Management

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/dsls/DSL-AshPostgres.DataLayer.md

Configure how a resource manages its tenant. This includes setting a template for the tenant schema, and whether to automatically create or update the tenant.

```elixir
manage_tenant do
  template ["organization_", :id]
  create? true
  update? false
end
```

--------------------------------

### Generate Resources from Database Tables

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/set-up-with-existing-database.md

Scaffolds AshPostgres resources for the specified domain based on a comma-separated list of table names. This is useful for quickly setting up resources from an existing database.

```bash
mix ash_postgres.gen.resources MyApp.MyDomain --tables table1,table2,table3
```

--------------------------------

### Generate Ash Migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/best_practices.md

Run this command after making resource changes to generate migration files. Use a descriptive name for clarity.

```bash
mix ash.codegen --name add_user_roles
```

```bash
mix ash.codegen --name implement_post_tagging
```

--------------------------------

### Configure Ecto Repo with Read Replicas

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/using-multiple-repos.md

Define multiple read-only Ecto repositories and a primary repository for mutations. The `replica/0` function randomly selects a read replica for queries.

```elixir
defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres

  @replicas [
    MyApp.Repo.Replica1,
    MyApp.Repo.Replica2,
    MyApp.Repo.Replica3,
    MyApp.Repo.Replica4
  ]

  def replica do
    Enum.random(@replicas)
  end

  for repo <- @replicas do
    defmodule repo do
      use Ecto.Repo,
        otp_app: :my_app,
        adapter: Ecto.Adapters.Postgres,
        read_only: true
    end
  end
end
```

--------------------------------

### Query with Calculations and Load On Demand

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Filter and sort representatives based on the 'percent_open' calculation, and explicitly load the calculation. This shows how calculations can be used in queries and loaded.

```elixir
require Ash.Query

Helpdesk.Support.Representative
|> Ash.Query.filter(percent_open > 0.25)
|> Ash.Query.sort(:percent_open)
|> Ash.Query.load(:percent_open)
|> Ash.read!()
```

--------------------------------

### Define Table-Specific Actions with set_context

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/polymorphic-resources.md

Use the `set_context` query preparation to define actions that operate on specific tables of a polymorphic resource. This allows for targeted data retrieval or manipulation.

```elixir
defmodule MyApp.Reaction do
  # ...
  actions do
    read :for_comments do
      prepare set_context(%{data_layer: %{table: "comment_reactions"}})
    end

    read :for_posts do
      prepare set_context(%{data_layer: %{table: "post_reactions"}})
    end
  end
end
```

--------------------------------

### Mark Resource for Context-Based Multitenancy

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/multitenancy.md

Designate a resource to be multitenant using the `:context` strategy. This requires specifying the attribute that holds the tenant identifier.

```elixir
defmodule MyApp.Post do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  multitenancy do
    strategy :context
    attribute :tenant
  end

  # Resource definition...
end
```

--------------------------------

### Generate Resources with Fragments and No Migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/working-with-existing-databases.md

Use this command to generate Ash resources from an existing database schema. It creates separate resource and fragment files, and disables migration generation.

```bash
mix ash_postgres.gen.resources MyApp.ExternalDb \
  --tables users,orders,products \
  --no-migrations \
  --fragments
```

--------------------------------

### PostgreSQL Table and Schema Configuration

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/configuration.md

Define PostgreSQL specific configurations for a resource. This includes setting the table name, schema, the Ecto repository to use, and whether migrations should be generated for this resource.

```elixir
postgres do
  # Required: Define the table name for this resource
  table "users"

  # Optional: Define the PostgreSQL schema
  schema "public"

  # Required: Define the Ecto repo to use
  repo MyApp.Repo

  # Optional: Control whether migrations are generated for this resource
  migrate? true
end
```

--------------------------------

### Implement Manual Relationship Loader

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/manual-relationships.md

Implements the `load/3` function for a manual relationship, fetching related records based on the source records and query context. It groups the results by the primary key of the source records.

```elixir
# in the resource

relationships do
  has_many :tickets_above_threshold, Helpdesk.Support.Ticket do
    manual Helpdesk.Support.Ticket.Relationships.TicketsAboveThreshold
  end
end

# implementation
defmodule Helpdesk.Support.Ticket.Relationships.TicketsAboveThreshold do
  use Ash.Resource.ManualRelationship
  use AshPostgres.ManualRelationship

  require Ash.Query
  require Ecto.Query

  def load(records, _opts, %{query: query, actor: actor, authorize?: authorize?}) do
    # Use existing records to limit resultds
    rep_ids = Enum.map(records, & &1.id)
     # Using Ash to get the destination records is ideal, so you can authorize access like normal
     # but if you need to use a raw ecto query here, you can. As long as you return the right structure.

    {:ok,
     query
     |> Ash.Query.filter(representative_id in ^rep_ids)
     |> Ash.Query.filter(priority > representative.priority_threshold)
     |> Helpdesk.Support.read!(actor: actor, authorize?: authorize?)
     # Return the items grouped by the primary key of the source, i.e representative.id => [...tickets above threshold]
     |> Enum.group_by(& &1.representative_id)}
  end

  # query is the "source" query that is being built.

  # _opts are options provided to the manual relationship, i.e `{Manual, opt: :val}`

  # current_binding is what the source of the relationship is bound to. Access fields with `as(^current_binding).field`

  # as_binding is the binding that your join should create. When you join, make sure you say `as: ^as_binding` on the
  # part of the query that represents the destination of the relationship

  # type is `:inner` or `:left`.
  # destination_query is what you should join to to add the destination to the query, i.e `join: dest in ^destination-query`
  def ash_postgres_join(query, _opts, current_binding, as_binding, :inner, destination_query) do
    {:ok,
     Ecto.Query.from(_ in query,
       join: dest in ^destination_query,
       as: ^as_binding,
       on: dest.representative_id == as(^current_binding).id,
       on: dest.priority > as(^current_binding).priority_threshold
     )}
  end

  def ash_postgres_join(query, _opts, current_binding, as_binding, :left, destination_query) do
    {:ok,
     Ecto.Query.from(_ in query,
       left_join: dest in ^destination_query,
       as: ^as_binding,
       on: dest.representative_id == as(^current_binding).id,
       on: dest.priority > as(^current_binding).priority_threshold
     )}
  end

  # _opts are options provided to the manual relationship, i.e `{Manual, opt: :val}`

  # current_binding is what the source of the relationship is bound to. Access fields with `parent_as(^current_binding).field`

  # as_binding is the binding that has already been created for your join. Access fields on it via `as(^as_binding)`

  # destination_query is what you should use as the basis of your query
  def ash_postgres_subquery(_opts, current_binding, as_binding, destination_query) do
    {:ok,
     Ecto.Query.from(_ in destination_query,
       where: parent_as(^current_binding).id == as(^as_binding).representative_id,
       where: as(^as_binding).priority > parent_as(^current_binding).priority_threshold
     )}
  end
end
```

--------------------------------

### Implement `all_tenants` Callback for Tenant Migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/schema-based-multitenancy.md

Implement the `all_tenants` callback in your AshPostgres repo to return a list of all schemas that require migration. This is crucial for running tenant-specific migrations.

```elixir
defmodule Myapp.Repo do
  use AshPostgres.Repo, ...

  import Ecto.Query, only: [from: 2]

  ...

  def all_tenants do
    all(from(row in "organizations", select: fragment("? || ?", "org_", row.id)))
  end
end
```

--------------------------------

### Configure Postgres Foreign Key References

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/foreign_keys.md

Defines foreign key references for a table. Use the `references` block to specify constraints, including simple references with defaults and fully configured references with options like `on_delete`, `on_update`, `name`, and `deferrable`.

```elixir
postgres do
  table "comments"
  repo MyApp.Repo

  references do
    # Simple reference with defaults
    reference :post

    # Fully configured reference
    reference :user,
      on_delete: :delete,
      on_update: :update,
      name: "comments_to_users_fkey",
      deferrable: true,
      initially_deferred: false
  end
end
```

--------------------------------

### Production Release Migration Task

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/development/migrations-and-tasks.md

Defines tasks for running migrations in a production release environment where 'mix' is not available. Includes functions for migrating the main database, tenant-specific migrations, and rolling back.

```elixir
defmodule MyApp.Release do
  @moduledoc """
Tasks that need to be executed in the released application (because mix is not present in releases).
  """
  @app :my_app
  def migrate do
    load_app()

    for repo <- repos() do
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
    end
  end

  # only needed if you are using postgres multitenancy
  def migrate_tenants do
    load_app()

    for repo <- repos() do
      path = Ecto.Migrator.migrations_path(repo, "tenant_migrations")
      # This may be different for you if you are not using the default tenant migrations

      {:ok, _, _} =
        Ecto.Migrator.with_repo(
          repo,
          fn repo ->
            for tenant <- repo.all_tenants() do
              Ecto.Migrator.run(repo, path, :up, all: true, prefix: tenant)
            end
          end
        )
    end
  end

  # only needed if you are using postgres multitenancy
  def migrate_all do
    load_app()
    migrate()
    migrate_tenants()
  end

  def rollback(repo, version) do
    load_app()
    {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :down, to: version))
  end

  # only needed if you are using postgres multitenancy
  def rollback_tenants(repo, version) do
    load_app()

    path = Ecto.Migrator.migrations_path(repo, "tenant_migrations")
    # This may be different for you if you are not using the default tenant migrations

    for tenant <- repo.all_tenants() do
      {:ok, _, _} =
        Ecto.Migrator.with_repo(
          repo,
          &Ecto.Migrator.run(&1, path, :down,
            to: version,
            prefix: tenant
          )
        )
    end
  end

  defp repos do
    domains()
    |> Enum.flat_map(fn domain ->
      domain
      |> Ash.Domain.Info.resources()
      |> Enum.map(&AshPostgres.DataLayer.Info.repo/1)
      |> Enum.reject(&is_nil/1)
    end)
    |> Enum.uniq()
  end

  defp domains do
    Application.fetch_env!(@app, :ash_domains)
  end

  defp load_app do
    Application.load(@app)
  end
end
```

--------------------------------

### Configure Custom Indexes with AshPostgres

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/dsls/DSL-AshPostgres.DataLayer.md

Use the `custom_indexes` DSL to define indexes for your Ash resources. This allows for complex index configurations beyond simple unique constraints provided by `identities`.

```elixir
custom_indexes do
  index [:column1, :column2], unique: true, where: "thing = TRUE"
end
```

--------------------------------

### Define Ash Postgres Repo

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Define your application's Ecto repository using AshPostgres.Repo. This wrapper provides Ash-specific functionalities. Ensure the otp_app matches your application's name.

```elixir
# in lib/helpdesk/repo.ex

defmodule Helpdesk.Repo do
  use AshPostgres.Repo, otp_app: :helpdesk

  def installed_extensions do
    # Ash installs some functions that it needs to run the
    # first time you generate migrations.
    ["ash-functions"]
  end
end
```

--------------------------------

### Extend Resource with AshPostgres

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Use the Ash CLI to extend an existing resource with AshPostgres capabilities. This command automatically adds the necessary configurations.

```bash
mix ash.patch.extend Helpdesk.Support.Ticket postgres
mix ash.patch.extend Helpdesk.Support.Representative postgres
```

--------------------------------

### Define Custom Postgres Statement (Simplified)

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/dsls/DSL-AshPostgres.DataLayer.md

Add a custom statement for migrations using the `statement` DSL. This is a direct way to define SQL for migration up and down.

```elixir
statement :pgweb_idx do
  up "CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));"
  down "DROP INDEX pgweb_idx;"
end
```

--------------------------------

### Executing Custom SQL Statements in PostgreSQL Migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/custom_sql_statements.md

Use `statement` to define SQL commands to be run during migration. Set `on_destroy: true` to execute the statement only when the resource is destroyed.

```elixir
postgres do
  custom_statements do
    statement "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\""

    statement """
    CREATE TRIGGER update_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION trigger_set_timestamp();
    """

    statement "DROP INDEX IF EXISTS posts_title_index",
      on_destroy: true # Only run when resource is destroyed/dropped
  end
end
```

--------------------------------

### Configure Ash Postgres Repo with Existing Ecto Repo

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

When integrating with an existing Ecto.Repo, set `define_ecto_repo?: false` to prevent AshPostgres from redefining it. This allows you to maintain your custom Ecto.Repo configuration.

```elixir
# in lib/helpdesk/repo.ex

defmodule Helpdesk.Repo do
  use Appsignal.Ecto.Repo,
    otp_app: :helpdesk,
    adapter: Ecto.Adapters.Postgres

  use AshPostgres.Repo,
    define_ecto_repo?: false

  def installed_extensions do
    ["ash-functions"]
  end
end
```

--------------------------------

### Configure Reference for a Relationship

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/dsls/DSL-AshPostgres.DataLayer.md

Configures the reference behavior for a relationship in resource migrations. Use this to define actions on delete or update for foreign keys.

```elixir
reference :post, on_delete: :delete, on_update: :update, name: "comments_to_posts_fkey"
```

--------------------------------

### Define Postgres References

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/dsls/DSL-AshPostgres.DataLayer.md

Configure references (foreign keys) for resource migrations. This is used by the migration generator to create foreign key constraints.

```elixir
references do
  reference :post, on_delete: :delete, on_update: :update, name: "comments_to_posts_fkey"
end
```

--------------------------------

### Access JSONB fields using `fragment/1`

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/expressions.md

Use `fragment/1` with PostgreSQL's `->` and `->>` operators to access JSONB fields. Interpolate the column name with `?` and provide the column as an argument.

```elixir
fragment("?->>'name' = ?", data, ^name)
```

--------------------------------

### Manual AshPostgres Configuration

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/tutorials/get-started-with-ash-postgres.md

Manually configure an Ash resource to use AshPostgres by specifying the table name and repository. This is done within the resource's Elixir module.

```elixir
# in lib/helpdesk/support/ticket.ex

  use Ash.Resource,
    domain: Helpdesk.Support,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "tickets"
    repo Helpdesk.Repo
  end
```

```elixir
# in lib/helpdesk/support/representative.ex

  use Ash.Resource,
    domain: Helpdesk.Support,
    data_layer: AshPostgres.DataLayer

  postgres do
    table "representatives"
    repo Helpdesk.Repo
  end
```

--------------------------------

### Configure Ecto Repo for Multitenancy Migrations

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/multitenancy.md

Modify your Ecto repository to provide all tenant schemas for migration purposes. The `all_tenants` function generates schema names based on a 'tenants' table.

```elixir
defmodule MyApp.Repo do
  use AshPostgres.Repo, otp_app: :my_app

  # Return all tenant schemas for migrations
  def all_tenants do
    import Ecto.Query, only: [from: 2]
    all(from(t in "tenants", select: fragment("? || ?", "tenant_", t.id)))
  end
end
```

--------------------------------

### Generate UUIDv7 Default Migration

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/development/upgrading-to-postgres-18.md

Run this command after updating `min_pg_version` to generate migrations that set the default for UUIDv7 attributes to the database's built-in function.

```bash
$ mix ash.codegen update_uuid_v7_default
```

--------------------------------

### Define a Polymorphic Resource

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/resources/polymorphic-resources.md

Configure a resource as polymorphic by setting `polymorphic? true` in the `postgres` configuration. This allows the `table` context to be dynamically set.

```elixir
defmodule MyApp.Reaction do
  use Ash.Resource,
    domain: MyDomain,
    data_layer: AshPostgres.DataLayer

  postgres do
    polymorphic? true # Without this, `table` is a required configuration
  end

  attributes do
    attribute :resource_id, :uuid, public?: true
  end

  ...
end
```

--------------------------------

### Configure Tenant Schema Management in AshResource

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/multitenancy.md

Define how tenant schemas are automatically created and managed by AshPostgres. The `manage_tenant` block specifies a template for schema names, using the tenant's ID.

```elixir
defmodule MyApp.Tenant do
  use Ash.Resource,
    data_layer: AshPostgres.DataLayer

  # Resource definition...

  postgres do
    table "tenants"
    repo MyApp.Repo

    # Automatically create/manage tenant schemas
    manage_tenant do
      template ["tenant_", :id]
    end
  end
end
```

--------------------------------

### Configure Multiple Repositories for Reads and Mutations

Source: https://github.com/ash-project/ash_postgres/blob/main/usage-rules/advanced_features.md

Configure Ash Postgres to use different Ecto repositories for read operations and mutations by providing a function to the `repo` configuration. This allows for the use of read replicas for reads and a primary database for writes.

```elixir
postgres do
  repo fn resource, type ->
    case type do
      :read -> MyApp.ReadReplicaRepo
      :mutate -> MyApp.WriteRepo
    end
  end
end
```

--------------------------------

### Configure AshPostgres Repo Argument

Source: https://github.com/ash-project/ash_postgres/blob/main/documentation/topics/advanced/using-multiple-repos.md

Specify the `repo` argument in the AshPostgres data layer to route read operations to a replica repository and mutation operations to the primary repository.

```elixir
defmodule MyApp.MyDomain.MyResource do
  use Ash.Resource,
    date_layer: AshPostgres.DataLayer

  postgres do
    table "my_resources"
    repo fn
      _resource, :read -> MyApp.Repo.replica()
      _resource, :mutate -> MyApp.Repo
    end
  end
end
```