[FEATURE]: Setup relational database structure

[Ravirkt]

Description

Design en setup database

I have done some research to find a way for a user to save evidence in the form and make this as accessible as possible, for example by not having to log in completely. The intention is also that the user is linked to their evidence while remaining as private as possible, so not with a name and other personal details.

Ultimately, I found a few options by searching online and with the help of AI.
These are the options that seemed interesting and could potentially work:

• Token stored in a cookie
• Token stored in the URL

Token Storage

1. Token stored in a cookie

When the user visits the website and fills in the form and submits it, a token is stored as a cookie.
When the user visits the site again later, the server reads the token from the cookie and knows which user it is.

Disadvantages

• The cookie can be deleted by the user (for example, by clearing browser history).
• The cookie does not work on other devices.

---

2. Token stored in the URL

When the user visits the website for the first time and submits their data, a token is generated and also visible in the URL.
This token is unique and linked to the user.
Through the URL containing this token, the server can identify the user and retrieve their data without sharing personal information.

Important

• The user must keep the token.
• They can save it in a Notes app, email the link to themselves, or write it down.

Conclusion

I did some more research and about the token saved in the url. I'm not sure why but this is not secure.

I eventually came to the magic link. This makes possible that the user only has to give in his email address. When the user sign is. They will get an link send to there email. That link gives them access to the there personal environment, so that the evidence they submit is linked to them.

In this message i have created an database structure: #86 (comment)

[Ravirkt]

Description

Relational database setup

This is how I envision the database. The user table has a one-to-many relationship with the evidence table.

---

erDiagram
    User ||--o{ Evidence :one_to_many

    User {
        User_id int PK
        Email varchar(64)
        Access_token varchar(64)
        Refresh_token varchar(64)
        expires varchar(64)

        Created_at datetime
    }

    Evidence {
        Evidence_id int PK
        User_id int FK
        Description varchar(900)
        Files JSONB
        Created_at datetime
    }

%% contact form 
    Questions {
        Question_id int PK
        First_name varchar(60)
        Last_name varchar(60)
        Email varchar(60)
        Message varchar(900)
    }

Loading

---

Mermaid editor

Example JSON structure

User

{
  "User_id": 1,
  "access_token": "abc123xyz456token",
  "Created_at": "2026-03-05T10:15:00Z",
  "expires": "29000",
  "refesh_token": "abc123xyz456token"
}

Evidence

{
  "Evidence_id": 13,
  "User_id": 1,
  "Description": "bla blsb lbla bla bla bla lamfakflsdfs",
  "Files": { },
  "Created_at": "2026-03-05T10:20:00Z"
}

Files object

{
  "files": [
    {
      "file_name": "image1.png",
      "id": "1",
      "directus_files_id": "f000f19a-2967-435b-8bc5-6653000a2e75"
    },
    {
      "file_name": "image1.png",
      "id": "1",
      "directus_files_id": "f000f19a-2967-435b-8bc5-6653000a2e75"
    }
  ]
}

[Ravirkt]

API endpoints

• Evidence: https://fdnd-agency.directus.app/items/digital_proof_empowerment_evidence
• Evidence files: https://fdnd-agency.directus.app/items/digital_proof_empowerment_evidence_files
• questions: https://fdnd-agency.directus.app/items/digital_proof_empowerment_questions
• user: https://fdnd-agency.directus.app/items/digital_proof_empowerment_users