# `Bylaw.Db.Adapters.Postgres.Checks.ScopedForeignKeys`
[🔗](https://github.com/ryanzidago/bylaw/blob/v0.1.0-alpha.1/lib/bylaw/db/adapters/postgres/checks/scoped_foreign_keys.ex#L1)

Validates that scoped Postgres foreign keys include configured scope columns.

## Examples

Before, both tables are tenant-scoped, but the foreign key only references
`conversations(id)`:

```sql
CREATE TABLE conversations (
  tenant_id uuid NOT NULL,
  id uuid NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (id)
);

CREATE TABLE messages (
  tenant_id uuid NOT NULL,
  conversation_id uuid NOT NULL,
  FOREIGN KEY (conversation_id) REFERENCES conversations(id)
);
```

A message can point at a conversation with the same `id` in another tenant if
application code passes the wrong identifier.

After, include the scope columns in the foreign key:

```sql
CREATE TABLE messages (
  tenant_id uuid NOT NULL,
  conversation_id uuid NOT NULL,
  FOREIGN KEY (tenant_id, conversation_id)
    REFERENCES conversations(tenant_id, id)
);
```

Postgres now enforces that the child and parent rows belong to the same
tenant, instead of relying on every query and write path to remember it.

## Notes

The check only applies when the child table and referenced table both have
every configured `scope_columns` column. Shared lookup tables that
intentionally have no tenant column are not flagged unless they match a
different rule.

## Options

A foreign key is checked when both the child table and referenced table have
every configured `:scope_columns` column. The foreign key must include those
columns on both sides so a child row cannot point at a parent row from another
scope:

```elixir
{Bylaw.Db.Adapters.Postgres.Checks.ScopedForeignKeys,
 rules: [
   [
     scope_columns: ["tenant_id", "workspace_id"],
     except: [[referenced_table: "global_settings"]]
   ]
 ]}
```

## Usage

Add this module to the checks passed to
`Bylaw.Db.Adapters.Postgres.validate/2`. See the
[README usage section](readme.html#usage) for the full ExUnit setup.

# `check_opt`

```elixir
@type check_opt() :: {:validate, boolean()} | {:rules, [rule()]}
```

# `check_opts`

```elixir
@type check_opts() :: [check_opt()]
```

# `matcher`

```elixir
@type matcher() :: [
  schema: matcher_values(),
  table: matcher_values(),
  constraint: matcher_values(),
  referenced_table: matcher_values()
]
```

# `matcher_value`

```elixir
@type matcher_value() :: String.t() | Regex.t()
```

# `matcher_values`

```elixir
@type matcher_values() :: matcher_value() | [matcher_value()]
```

# `rule`

```elixir
@type rule() :: [
  only: matcher() | [matcher()],
  except: matcher() | [matcher()],
  scope_columns: [String.t()]
]
```

# `validate`

```elixir
@spec validate(target :: Bylaw.Db.Target.t(), opts :: check_opts()) ::
  Bylaw.Db.Check.result()
```

Implements the `Bylaw.Db.Check` validation callback.

---

*Consult [api-reference.md](api-reference.md) for complete listing*