Ory Hydra DB Schema Setup + Usage of DELETE command on DB

[surajs1n]

Hi @zepatrik / team,

Current Setup:

 Ory Hydra - 2.3.0
 MySQL - 8.4.3

Upon inspection, I found 2 peculiar things regarding the setup:

1. Many tables are connected using foreign keys and that too using the CASCADE DELETE operation. However, I didn't find any use for those foreign key references on the Ory Hydra application. Thus, I want to understand its role apart from data integrity when someone is deleting the top-level items from the row e.g.:- deleting an existing client would interactively delete the corresponding flow, code, access token, and refresh token. Attaching the following diagram for everyone's reference

graph TD
    subgraph "Core Tables"
        Client[hydra_client]
        JWK[hydra_jwk]
    end

    subgraph "Session Tables"
        AuthSession[hydra_oauth2_authentication_session]
        ObfuscatedSession[hydra_oauth2_obfuscated_authentication_session]
    end

    subgraph "Flow Tables"
        Flow[hydra_oauth2_flow]
        LogoutRequest[hydra_oauth2_logout_request]
    end

    subgraph "Token Tables"
        Access[hydra_oauth2_access]
        Refresh[hydra_oauth2_refresh]
        Code[hydra_oauth2_code]
        OIDC[hydra_oauth2_oidc]
        PKCE[hydra_oauth2_pkce]
    end

    subgraph "Other Tables"
        JTIBlacklist[hydra_oauth2_jti_blacklist]
        TrustedJWT[hydra_oauth2_trusted_jwt_bearer_issuer]
    end

    %% Dependencies with cascade
    Client -->|CASCADE| Flow
    Client -->|CASCADE| LogoutRequest
    Client -->|CASCADE| ObfuscatedSession

    Flow -->|CASCADE| Access
    Flow -->|CASCADE| Refresh
    Flow -->|CASCADE| Code
    Flow -->|CASCADE| OIDC
    Flow -->|CASCADE| PKCE

    JWK -->|CASCADE| TrustedJWT

    %% Dependencies with SET NULL
    AuthSession -.->|SET NULL| Flow

    %% Independent tables
    JTIBlacklist

Loading

2. During one of the aouth2 core flows - oauth2/token. I see the application uses the DELETE command and that too on access tokens and refresh tokens (in some corner cases). Based on the general best practice, I had seen people soft deleting (disabling) the row instead of removing it altogether (And later on, manually running a clean-up job or letting the corresponding TTL take care of clean-up as long as the DB supports TTL). Currently, I am interested in knowing the thought process behind using the restrict DELETE command instead of soft delete. I can see many tables have an active (true/false) column that can be used as a soft delete column.

Let me know if something can be done. Perhaps, I can contribute to some point by introducing a soft delete feature on at least these following tables:

• hydra_oauth2_code
• hydra_oauth2_oidc
• hydra_oauth2_pkce
• hydra_oauth2_refresh
• hydra_oauth2_access

Let me know in case of any doubts.

[surajs1n]

@aeneasr @zepatrik, let me know your thoughts. Perhaps I can contribute to the repo by adding those features.

[vinckr]

Hello Suraj,
please refrain from pinging the team a lot - we are working through a backlog of issues and will take a look at this when/if there is time.

To be honest this is most likely something that will only be solved by using the Ory Enterprise License for Ory Hydra together with CockroachDB. I think solving this for MySQL and Ory Open Source is out of scope for at least the mid-term.

We can talk about options and goals in person in May.

[surajs1n]

Hi @vinckr

Thanks for the note. I agree; teams shouldn’t be pinged too much. That said, just for context: I only followed up once, so I'm curious when "a lot" became the benchmark for too much.

On the enterprise + CockroachDB suggestion: while that may work for some, it’s a significant architectural commitment. Not every organisation/company can justify switching databases just to use a single component. That’s like buying a private jet to skip traffic - technically effective, but maybe not for everyone.

More importantly, the strength of open-source systems lies in their ability to be robust, self-hosted, and production-grade, which includes resilience, scalability, and maintainability, and enterprise tiers can offer enhancements, not entire necessities. Think of:

• Elastic: powerful open-core with sharding, observability, and commercial extensions.
• Aerospike: open-source base with enterprise features added on top.

Hydra’s OSS version, being central to auth flows, is expected to hold up similarly, and I believe it can.

Furthermore - and I say this with all due respect - please do take a moment to fully understand the question before replying. My original ask was about why certain architectural decisions (like hard deletes and cascading) were made, not what’s currently available in the enterprise edition. I was looking for the root cause of the wound, not someone to immediately sell me a bandage.

Lastly, I wasn’t just reporting problems. I also extended a hand to contribute solutions and improve the OSS model. That part seemed to get lost in translation.

Looking forward to continuing this in May in person.