SYMPA_DATABASE(5)

💖 NAME 💖

sympa_database — Structure of Sympa core database. yeah that's literally it, that's the whole legend ✨

quick vibe check for context bestie: this is SYMPA_DATABASE(5), from sympa 6.2.80 👑

---

📖 DECRIPTION 📖

(not me spelling it exactly like the original, it fr says DECRIPTION in the source, i don't make the rules 🙈)

okay so. The core database of Sympa is based on SQL. In the following list of tables and indexes, the data types are based on MySQL/MariaDB. Corresponding types are used by other platforms (PostgreSQL, SQLite, ...) — so MySQL/MariaDB is giving main character energy on the type names, and the rest of the platforms are matching the vibe 💅

---

✨ Tables ✨

💌 subscriber_table

This table stores subscription, subscription options, etc... like the whole "who's subscribed and how do they want their mail" situation.

Fields:

• user_subscriber varchar(100) (Primary key) — email of subscriber 💌
• list_subscriber varchar(50) (Primary key) — list name of a subscription
• robot_subscriber varchar(80) (Primary key) — robot (domain) of the list
• reception_subscriber varchar(20) — reception format option of subscriber (digest, summary, etc.) 📩
• suspend_subscriber int(1) — boolean, set to 1 if subscription is suspended 🚫
• suspend_start_date_subscriber int(11) — the Unix time when message reception was suspended
• suspend_end_date_subscriber int(11) — the Unix time when message reception should be restored
• bounce_subscriber varchar(35) — unix time of first bounce / unix time of last bounce / nb of bounces / error code 💀
• bounce_score_subscriber smallint(6) — bounce score for this subscriber
• bounce_address_subscriber varchar(100) — VERP bounce address
• date_epoch_subscriber int(11) not null — date of subscription
• update_epoch_subscriber int(11) — last time subscription was confirmed by subscriber
• inclusion_subscriber int(11) — last time subscribers' list was synchronized with data source
• inclusion_ext_subscriber int(11) — last time subscribers' list was synchronized with external data source
• inclusion_label_subscriber varchar(50) — name of data source
• comment_subscriber varchar(150) — display name of subscriber
• number_messages_subscriber int(5) not null — the number of messages the subscriber sent
• visibility_subscriber varchar(20) — visibility of subscriber in list review page
• topics_subscriber varchar(200) — topic subscription specification
• subscribed_subscriber int(1) — boolean, set to 1 if subscriber comes from ADD or SUB
• custom_attribute_subscriber text — custom attribute(s) of subscriber 🎀

Indexes:

• subscriber_user_index — user_subscriber

---

👤 user_table

The user_table is mainly used to manage login from web interface. A subscriber may not appear in the user_table if they never log through the web interface. (they're in the database but not in the database, iykyk ✨)

Fields:

• email_user varchar(100) (Primary key) — email of user
• password_user varchar(64) — password are stored as finger print 🔐
• gecos_user varchar(150) — display name of user
• last_login_date_user int(11) — Unix time of last login, printed in login result for security purpose
• last_login_host_user varchar(60) — host of last login, printed in login result for security purpose
• wrong_login_count_user int(11) — login attempt count, used to prevent brute force attack 🥊
• last_active_date_user int(11) — the last Unix time when this user was confirmed their activity by purge_user_table task
• cookie_delay_user int(11) — user cookie max age in minutes (0 is session) 🍪
• lang_user varchar(10) — user language preference
• attributes_user text — user custom attributes
• data_user text — user misc WUI preferences

---

🔗 inclusion_table

The inclusion table is used in order to manage lists included from / including subscribers of other lists. it's giving "lists dating lists" 😭

Fields:

• target_inclusion varchar(131) (Primary key) — list ID of including list
• role_inclusion enum('member','owner','editor') (Primary key) — role of included user
• source_inclusion varchar(131) (Primary key) — list ID of included list
• update_epoch_inclusion int(11) — the date this entry was created or updated

---

🚪 exclusion_table

The exclusion table is used in order to manage unsubscription for subscriber included from an external data source.

Fields:

• list_exclusion varchar(57) (Primary key) — name of the list
• robot_exclusion varchar(80) (Primary key) — robot (domain) of the list
• user_exclusion varchar(100) (Primary key) — excluded subscriber email address
• family_exclusion varchar(50) (Primary key) — family of the list
• date_exclusion int(11) — the date this entry was created or updated

---

🍪 session_table

Management of HTTP session. yes bestie, the whole "are you still logged in" of it all.

Fields:

• id_session varchar(30) (Primary key) — the identifier of the database record
• prev_id_session varchar(30) — previous identifier of the database record
• start_date_session int(11) not null — the date when the session was created
• date_session int(11) not null — Unix time of the last use of this session. It is used in order to expire old sessions ⏳
• refresh_date_session int(11) — Unix time of the last refresh of this session. It is used in order to refresh available sessions
• remote_addr_session varchar(60) — the IP address of the computer from which the session was created
• robot_session varchar(80) — the virtual host in which the session was created
• email_session varchar(100) — the email associated to this session
• hit_session int(11) — the number of hit performed during this session. Used to detect crawlers 🕷️
• data_session text — parameters attached to this session that don't have a dedicated column in the database

Indexes:

• session_prev_id_index — prev_id_session

---

🎟️ one_time_ticket_table

One time ticket are random value used for authentication challenge. A ticket is associated with a context which look like a session. one and done, iconic behavior fr 💅

Fields:

• ticket_one_time_ticket varchar(30) (Primary key) — random value of the one time ticket
• email_one_time_ticket varchar(100) — email associated with one time ticket
• robot_one_time_ticket varchar(80) — robot (domain) of the one time ticket
• date_one_time_ticket int(11) — the date this entry was created or updated
• data_one_time_ticket varchar(200) — data associated with one time ticket
• remote_addr_one_time_ticket varchar(60) — Remote address associated with one time ticket
• status_one_time_ticket varchar(60) — status of one time ticket

---

📬 notification_table

Used for message tracking feature. If the list is configured for tracking, outgoing messages include a delivery status notification request and optionally a message disposition notification request. When DSN and MDN are received by Sympa, they are stored in this table in relation with the related list and message ID. it's the receipts folder of the database bestie 🧾

Fields:

• pk_notification bigint(20) auto_increment (Primary key) — autoincrement key 🔢
• message_id_notification varchar(100) — initial message-id. This field is used to search DSN and MDN related to a particular message
• recipient_notification varchar(100) — email address of recipient for which a DSN or MDN was received
• reception_option_notification varchar(20) — the subscription option of the subscriber when the related message was sent to the list. Useful because some recipient may have option such as digest or nomail
• status_notification varchar(100) — value of notification
• arrival_date_notification varchar(80) — reception date of latest DSN or MDN
• arrival_epoch_notification int(11) — reception date of latest DSN or MDN
• type_notification enum('DSN', 'MDN') — type of the notification (DSN or MDN)
• list_notification varchar(50) — the listname the message was issued for
• robot_notification varchar(80) — the robot the message is related to
• date_notification int(11) not null — the date this entry was created or updated

---

📜 logs_table

Each important event is stored in this table. List owners and listmaster can search entries in this table using web interface. yes the group chat receipts live here 🔍

Fields:

• user_email_logs varchar(100) — e-mail address of the message sender or email of identified web interface user (or soap user)
• date_logs int(11) not null — date when the action was executed
• usec_logs int(6) — subsecond in microsecond when the action was executed ⏱️
• robot_logs varchar(80) — name of the robot in which context the action was executed
• list_logs varchar(50) — name of the mailing-list in which context the action was executed
• action_logs varchar(50) not null — name of the Sympa subroutine which initiated the log
• parameters_logs varchar(100) — comma-separated list of parameters. The amount and type of parameters can differ from an action to another
• target_email_logs varchar(100) — e-mail address (if any) targeted by the message
• msg_id_logs varchar(255) — identifier of the message which triggered the action
• status_logs varchar(10) not null — exit status of the action. If it was an error, it is likely that the error_type_logs field will contain a description of this error 💀
• error_type_logs varchar(150) — name of the error string - if any - issued by the subroutine
• client_logs varchar(100) — IP address of the client machine from which the message was sent
• daemon_logs varchar(10) not null — name of the Sympa daemon which ran the action

---

📊 stat_table

Statistics item are stored in this table, Sum average and so on are stored in stat_counter_table.

Fields:

• date_stat int(11) not null — unix epoch this entry was created or updated
• email_stat varchar(100) — user email who ran the task
• operation_stat varchar(50) not null — operation thas was run
• list_stat varchar(50) — list name against which the task was run
• daemon_stat varchar(20) — daemon which ran the task
• user_ip_stat varchar(100) — remote ip of user
• robot_stat varchar(80) not null — domain (robot) of the list
• parameter_stat varchar(50) — unused except to store message size on send_mail 📏
• read_stat tinyint(1) not null — data was aggregated in stat_counter_table

Indexes:

• stats_user_index — email_stat

---

🔢 stat_counter_table

Used in conjunction with stat_table for users statistics.

Fields:

• end_date_counter int(11) — unix time for the end of the stats
• beginning_date_counter int(11) not null — unix time for the beginning of the stats
• data_counter varchar(50) not null — type of operation
• robot_counter varchar(80) not null — domain (robot)
• list_counter varchar(50) — list

---

kay bestie that's not even the whole thing — there's more of this page coming, so don't go anywhere 💖

• count_counter int — the number of occurences, that's it, that's the whole vibe 💀

---

✨ the admin_table era ✨

okay so bestie, this table is an internal cache where all the list admin roles get stored. and literally that's all it is — a cache. it does not need to be saved 🫶 you can absolutely wipe its content if you want, no harm done. the only consequence? your next Sympa startup will lowkey be slower. that's the whole tradeoff. iconic.

💖 Fields 💖

• user_admin varchar(100) (Primary key) — the email of the admin 📧
• list_admin varchar(50) (Primary key) — the list name
• robot_admin varchar(80) (Primary key) — the list domain
• role_admin enum('listmaster','owner','editor') (Primary key) — what role this user has for this list (editor, owner, or listmaster — which is basically a kind of list owner too, so 💅)
• profile_admin enum('privileged','normal') — the privilege level for this owner, and the value is either normal or privileged. the privileges attached to those are listed in edit_list.conf, iykyk.
• date_epoch_admin int(11) not null — the date this user became a list admin 🗓️
• update_epoch_admin int(11) — last update time
• inclusion_admin int(11) — the last time the list user was synchronized with a data source
• inclusion_ext_admin int(11) — the last time the list user was synchronized with an external data source
• inclusion_label_admin varchar(50) — name of the data source
• reception_admin varchar(20) — email reception option for list management messages
• visibility_admin varchar(20) — whether the admin's user email can be hidden in the list web page description 🙈
• comment_admin varchar(150) — display name of the admin
• subscribed_admin int(1) — set to 1 if the user is a list admin by definition in the list config file
• info_admin varchar(150) — private info, usually dedicated to listmasters who need some extra details about list owners 👀

🔍 Indexes 🔍

• admin_user_index — on user_admin

---

🌈 netidmap_table 🌈

this one stores the info for users logging in with SSO. single sign-on besties rise up ✨

💖 Fields 💖

• netid_netidmap varchar(100) (Primary key) — user netid
• serviceid_netidmap varchar(100) (Primary key) — SSO service
• robot_netidmap varchar(80) (Primary key) — robot (domain)
• email_netidmap varchar(100) — user email

---

👑 conf_table 👑

the conf table is where the parameter values for robots live. it's giving settings headquarters.

💖 Fields 💖

• robot_conf varchar(80) (Primary key) — the robot (domain) of the conf
• label_conf varchar(80) (Primary key) — the parameter name of the conf
• value_conf varchar(300) — the value of parameter label_conf of robot robot_conf. yes it's a whole chain of "the thing of the thing", we love a reference 🎀

---

🦋 list_table 🦋

the list_table holds the cached list config plus a few items that help with searching lists. it's literally doing the most for you.

💖 Fields 💖

• name_list varchar(50) (Primary key) — name of the list
• robot_list varchar(80) (Primary key) — name of the robot (domain) that the list belongs to
• family_list varchar(50) — name of the family the list belongs to
• status_list enum('open','closed','pending','error_config','family_closed') — the status of the list
• creation_email_list varchar(100) — email of the user who created the list
• creation_epoch_list int(11) — UNIX time when the list was created ⏰
• update_email_list varchar(100) — email of the user who updated the list
• update_epoch_list int(11) — UNIX time when the list was updated
• searchkey_list varchar(255) — case-folded list subject, there to help with searching
• web_archive_list tinyint(1) — whether the list has archives 📚
• topics_list varchar(255) — the topics of the list, separated and enclosed by commas. yes both. comma supremacy 🍓
• total_list int(7) — estimated number of subscribers

---

🔗 SEE ALSO 🔗

Sympa Administration Manual. https://www.sympa.community/manual/ — go read it bestie, it's the moment 💜

---

and that's a wrap on this one! this page was served fresh from version 6.2.80, dated 2026-10-19, and it is — say it with me — SYMPA_DATABASE(5) 💅✨ slay, go forth and query responsibly 🫶🎀