# Which Problems Are Solved
As an outcome of [this
issue](https://github.com/zitadel/zitadel/issues/9599) we want to
implement relational tables in Zitadel. For that we use new tables as a
successor of the current tables used by Zitadel in `projections`, `auth`
and `admin` schemas. The new logic is based on [this
proposal](https://github.com/zitadel/zitadel/pull/9870). This issue does
not contain the switch from CQRS to the new tables. This is change will
be implemented in a later stage.
We focus on the most critical tables which is user authentication.
We need a table to manage organizations.
### organization fields
The following fields must be managed in this table:
- `id`
- `instance_id`
- `name`
- `state` enum (active, inactive)
- `created_at`
- `updated_at`
- `deleted_at`
DISCUSS: should we add a `primary_domain` to this table so that we do
not have to join on domains to return a simple org?
We must ensure the unique constraints for this table matches the current
commands.
### organization repository
The repository must provide the following functions:
Manipulations:
- create
- `instance_id`
- `name`
- update
- `name`
- delete
Queries:
- get returns single organization matching the criteria and pagination,
should return error if multiple were found
- list returns list of organizations matching the criteria, pagination
Criteria are the following:
- by id
- by name
pagination:
- by created_at
- by updated_at
- by name
### organization events
The following events must be applied on the table using a projection
(`internal/query/projection`)
- `org.added` results in create
- `org.changed` sets the `name` field
- `org.deactivated` sets the `state` field
- `org.reactivated` sets the `state` field
- `org.removed` sets the `deleted_at` field
- if answer is yes to discussion: `org.domain.primary.set` sets the
`primary_domain` field
- `instance.removed` sets the the `deleted_at` field if not already set
### acceptance criteria
- [x] migration is implemented and gets executed
- [x] domain interfaces are implemented and documented for service layer
- [x] repository is implemented and implements domain interface
- [x] testing
- [x] the repository methods
- [x] events get reduced correctly
- [x] unique constraints
# Additional Context
Replace this example with links to related issues, discussions, discord
threads, or other sources with more context.
Use the Closing #issue syntax for issues that are resolved with this PR.
- Closes #https://github.com/zitadel/zitadel/issues/9936
---------
Co-authored-by: adlerhurst <27845747+adlerhurst@users.noreply.github.com>
<!--
Please inform yourself about the contribution guidelines on submitting a
PR here:
https://github.com/zitadel/zitadel/blob/main/CONTRIBUTING.md#submit-a-pull-request-pr.
Take note of how PR/commit titles should be written and replace the
template texts in the sections below. Don't remove any of the sections.
It is important that the commit history clearly shows what is changed
and why.
Important: By submitting a contribution you agree to the terms from our
Licensing Policy as described here:
https://github.com/zitadel/zitadel/blob/main/LICENSING.md#community-contributions.
-->
# Which Problems Are Solved
Implementing Instance table to new relational database schema
# How the Problems Are Solved
The following fields must be managed in this table:
- `id`
- `name`
- `default_org_id`
- `zitadel_project_id`
- `console_client_id`
- `console_app_id`
- `default_language`
- `created_at`
- `updated_at`
- `deleted_at`
The repository must provide the following functions:
Manipulations:
- create
- `name`
- `default_org_id`
- `zitadel_project_id`
- `console_client_id`
- `console_app_id`
- `default_language`
- update
- `name`
- `default_language`
- delete
Queries:
- get returns single instance matching the criteria and pagination,
should return error if multiple instances were found
- list returns list of instances matching the criteria, pagination
Criteria are the following:
- by id
pagination:
- by created_at
- by updated_at
- by name
### instance events
The following events must be applied on the table using a projection
(`internal/query/projection`)
- `instance.added` results in create
- `instance.changed` changes the `name` field
- `instance.removed` sets the the `deleted_at` field
- `instance.default.org.set` sets the `default_org_id` field
- `instance.iam.project.set` sets the `zitadel_project_id` field
- `instance.iam.console.set` sets the `console_client_id` and
`console_app_id` fields
- `instance.default.language.set` sets the `default_language` field
- if answer is yes to discussion: `instance.domain.primary.set` sets the
`primary_domain` field
### acceptance criteria
- [x] migration is implemented and gets executed
- [x] domain interfaces are implemented and documented for service layer
- [x] repository is implemented and implements domain interface
- [x] testing
- [x] the repository methods
- [x] events get reduced correctly
- [x] unique constraints
# Additional Context
- Closes https://github.com/zitadel/zitadel/issues/9935
