# Flagged enums

Map a C# flags enum to a many-to-many junction table with [FlaggedEnum] in Socigy.OpenSource.DB v0.3.2.

## Overview

A flags enum represents a set of values, for example a user's roles. Instead of storing a bitmask integer, `[FlaggedEnum]` instructs the generator to manage a **junction table**: one row per enum flag that is set. This lets you query all users with a specific role efficiently.

## Declare the enum table

The enum carries `[Table]` to make it a reference table:

```csharp
[Table("roles")]
public enum Role
{
    Reader    = 1,
    Writer    = 2,
    Moderator = 4,
    Admin     = 8,
}
```

The migration tool seeds this table with all declared enum values. Values must be powers of two; they are stored as the `id` column.

## Declare the owner model

Add a `[FlaggedEnum]` property on the model that holds the combined flags value. The property type must be the flags enum:

```csharp
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }

    [FlaggedEnum]
    public Role Roles { get; set; }
}
```

## Generated junction table

The source generator creates a junction table named `{main_table}_{enum_table}` with two foreign key columns:

```sql
CREATE TABLE "users_roles" (
    "users_id"  UUID    NOT NULL,
    "roles_id"  INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id")  REFERENCES "users"("id")  ON DELETE CASCADE,
    FOREIGN KEY ("roles_id")  REFERENCES "roles"("id")
);
```

Column naming convention:
- Main table FK: `{main_table}_{pk_column}`, giving `users_id`.
- Enum FK: `{enum_table}_id`, giving `roles_id`.

You can override individual mappings by passing alternating `(localPropertyName, junctionColumnName)` pairs: `[FlaggedEnum(nameof(Id), "user_id")]`. Override the junction table name with the `TableName` property.

## Reading and writing

When you insert or query a `User`, the `Roles` property is treated as a combined flags bitmask. The generated code decomposes the bitmask into individual flag values and syncs the junction table rows.

```csharp
var user = new User
{
    Username = "alice",
    Roles    = Role.Reader | Role.Writer,
};
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
```

When a user is read back, the junction table rows are aggregated into the `Roles` bitmask:

```csharp
await foreach (var u in User.Query(x => x.Username == "alice")
    .WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Roles.HasFlag(Role.Writer)); // True
}
```

## DDL setup

You must create the enum table and seed it before using the junction table. The CLI tool does this automatically; see [Schema generation](/database/0.3.2/migration/schema-generation). For manual setup:

```sql
CREATE TABLE "roles" (
    "id"          INTEGER NOT NULL,
    "value"       TEXT    NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);
INSERT INTO "roles" ("id", "value", "description") VALUES
    (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL);

CREATE TABLE "users_roles" (
    "users_id" UUID    NOT NULL,
    "roles_id" INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("roles_id") REFERENCES "roles"("id")
);
```

> **NOTE** When the junction table needs extra columns (an `AssignedAt` timestamp, for instance), declare an explicit `[FlagTable]` class and point to it with `[FlaggedEnumTable(typeof(...))]` instead of `[FlaggedEnum]`. See the Tables article.

> **WARNING** Enum member values must be exact powers of two (1, 2, 4, 8, and so on). Arbitrary integers break the bitmask decomposition.