Soft delete, auditing, migration history tables, PostgreSQL identifier

[khoffrath]

Hi @JonPSmith!

First thank you very much for this project, the documentation and all the work and effort you put into it!

I'm currently in the planning and research state for a few projects and AuthP ticks many feature boxes. I worked through the blog articles and most of the wiki documentation and also played a bit with Example 4, without diving too deep into the library code, though. I found a few things for which I would like to pick your opinion, if this is the wrong medium let me know and I'll create issues:

• I would like to utilize your SoftDeleteService (https://github.com/JonPSmith/EfCore.SoftDeleteServices) or any soft delete feature in the AuthP context. I think I'll have to adapt the saving and loading mechanisms of AuthP, can you give any hints how to go about this?
• And the most import feature: auditing the AuthP context. I would like to log/record any changes that happen within the AuthP db context. Preferrably not via temporal tables, but by using the Audit.NET / Audit.EntityFramework packet (https://github.com/thepirat000/Audit.NET/blob/master/src/Audit.EntityFramework) or another mechanism. Audit.EntityFramework requires changes to the db context that is audited: https://github.com/thepirat000/Audit.NET/blob/master/src/Audit.EntityFramework/README.md#usage. I guess the only way to implement this would be to create a fork with the necessary changes?
• I noticed that the migration history tables are created in the "main" schema (SQL Server: dbo). I expected them to be created in the respective schema of the managed tables, e.g. schema "authp" for the AuthP tables. This way the history table wouldn't need a unique name and restoring a backup of the AuthP database / schema would be easier
• And a cosmetic issue: Npgsql offers a configuration option UseSnakeCaseNamingConvention (https://github.com/efcore/EFCore.NamingConventions#supported-naming-conventions) that converts table and column names to lowercase format so that if one queries the database directly the identifiers don't have to be quoted. Do you see any way to sneak this into AuthP? Again this is a minor issue as I normally wouldn't query the AuthP database manually.

Thank you very much!

[JonPSmith]

Hi @khoffrath,

I will endeavour to answer your questions.

I would like to utilize your SoftDeleteService in the AuthP context.

When use say "...the AuthP context" I think you are thinking about the data your want to hold in each tenant and not the AuthP users, roles etc. See my answer to your migration tables about different parts of the application data is managed separately.

If that is what you mean you certainly use my SoftDeleteService, as it is specifically designed to work on tenant data. That's because a client wanted a multi-tenant app with soft delete (I talk about that project in this ASP.NET community standup on multi-tenant apps).

The SoftDeleteService can handle query filters with multiple parts (see the Multiple Query filters document). This means you can soft delete a entity while still keeping the tenant filter (in AuthP that is known as the DataKey.

NOTE: You can't soft delete the AuthP users, roles etc. The AuthUser does have a IsDisabled feature where if a user is disabled then their Auth Claims aren't added to their ASP.NET Core User.

Auditing the AuthP context

Again, the "...the AuthP context" is ambiguous. You create your own DbContext for the data you want to manage, so you can do whatever you want.

If you really want to audit the AuthP users, roles etc. there is a way to do that. There is a way to detect changes, which - see this AuthP document and this article.

I noticed that the migration history tables are created in the "main" schema

I have used different names for the migrations, but in the default schema. Your idea is good, but I can't change things now as that makes it much more difficult for a project to move up the the next version. I work hard to make new versions from having breaking changes.

Npgsql offers a configuration option UseSnakeCaseNamingConvention

Of course you can do that on your DbContext for your data, but I'm not going to change the AuthP for one user. I will look at whether I can give you access the options when setting up the contexts as I have done that in my EfCore.TestSupport library.

Final note

Be aware that I am still working on this library. At the moment I'm improving ways to handle the linking of ASP.NET Core authentication handles to AuthP. After that I'm looking at adding services fro setting sharding databases using Azure SQL Database elastic pools.

[khoffrath]

Hi @JonPSmith,

thanks for your detailled reply. Sorry when I was unclear when I said "AuthP context", actually I meant the context that contains the AuthP users, roles, etc. As you said, adding the SoftDelete service and auditing to the db context for tenant data is not a problem because we have full control over the db context.

SoftDelete service

If it's not possible to add soft delete to the context holding the AuthP users, roles, etc. we'll can work around it by setting the IsDisabled feature on AuthUser and handle the deletion/inactivation of the ASP.NET identity user separately.

Auditing AuthP users, roles...

Auditing the AuthP users and roles, especially actions of tenant admins is an important feature. I missed the documentation how to detect changes you linked to and will check it out, thank you.

Location of migration history tables

Preventing breaking changes is always nice and makes upgrading easier 💯 . After I posted the question it occurred to me that even if we use the database sharding feature, this wouldn't be a problem because the sharding feature is based on databases and not schemas. Since every shard is a separate database, the locaction of the history tables is mostly cosmetic.

UseSnakeCaseNamingConvention

Understood and as I wrote it's mostly cosmetic. Please don't put any effort to expose this option. If we'll eventually have to query the tables directly we can quote the identifiers.

Thank you again for your time and input 😃