I ran into the issue when a ldap user was deleted the user Deck boards also got removed from the database. This makes it difficult to retrieve the data the normal way. I created this script with AI to export a Deckboard directly from a database backup. If anyone has a better solution feel free to share.
To use restore a database backup to MySQL/MariaDB and retrieve the board id of the board you want to export.
SELECT * FROM oc_deck_boards;
Next update the script below with you details and run it on the database server.
import mysql.connector
import json
BOARD_ID = xxx # Change to your board ID
conn = mysql.connector.connect(
user="DBUSERHERE",
password="DBPASSWORDHERE",
database="DBNAMEHERE",
unix_socket='/run/mysqld/mysqld.sock'
)
cursor = conn.cursor(dictionary=True)
def fetch_table_where(table, column, value):
cursor.execute(f"SELECT * FROM {table} WHERE {column} = %s", (value,))
return cursor.fetchall()
# Fetch base board data
board = fetch_table_where("oc_deck_boards", "id", BOARD_ID)[0]
acls_raw = fetch_table_where("oc_deck_board_acl", "board_id", BOARD_ID)
labels = fetch_table_where("oc_deck_labels", "board_id", BOARD_ID)
stacks_raw = fetch_table_where("oc_deck_stacks", "board_id", BOARD_ID)
stack_ids = tuple(stack['id'] for stack in stacks_raw) or (0,)
# Cards
cursor.execute(
f"SELECT * FROM oc_deck_cards WHERE stack_id IN ({','.join(['%s'] * len(stack_ids))})", stack_ids
)
cards_raw = cursor.fetchall()
card_ids = tuple(card['id'] for card in cards_raw) or (0,)
# Attachments, labels, users
cursor.execute(f"SELECT * FROM oc_deck_attachment WHERE card_id IN ({','.join(['%s'] * len(card_ids))})", card_ids)
attachments = cursor.fetchall()
cursor.execute(f"SELECT * FROM oc_deck_assigned_labels WHERE card_id IN ({','.join(['%s'] * len(card_ids))})", card_ids)
assigned_labels = cursor.fetchall()
cursor.execute(f"SELECT * FROM oc_deck_assigned_users WHERE card_id IN ({','.join(['%s'] * len(card_ids))})", card_ids)
assigned_users = cursor.fetchall()
# ACLs with participant user info
acl = []
for a in acls_raw:
cursor.execute("SELECT uid, displayname FROM oc_users WHERE uid = %s", (a['participant'],))
u = cursor.fetchone() or {"uid": a['participant'], "displayname": a['participant']}
acl.append({
"id": a.get("id"),
"participant": {
"primaryKey": a['participant'],
"uid": u['uid'],
"displayname": u['displayname'],
"type": a.get('type', 0)
},
"type": a.get('type', 0),
"boardId": a.get('board_id'),
"permissionEdit": bool(a.get('permission_edit', False)),
"permissionShare": bool(a.get('permission_share', False)),
"permissionManage": bool(a.get('permission_manage', False)),
"owner": False
})
# Build export JSON
export = {
"version": "1.15.1",
"boards": {
str(board["id"]): {
"id": board["id"],
"title": board["title"],
"owner": {
"primaryKey": board["owner"],
"uid": board["owner"],
"displayname": board["owner"],
"type": 0
},
"color": board.get("color", "000000"),
"archived": False,
"labels": labels,
"acl": acl,
"permissions": {
"PERMISSION_READ": True,
"PERMISSION_EDIT": True,
"PERMISSION_MANAGE": True,
"PERMISSION_SHARE": True
},
"users": [],
"shared": 0,
"stacks": {},
"activeSessions": [],
"deletedAt": 0,
"lastModified": 0,
"settings": [],
"ETag": ""
}
}
}
# Cards + stacks
for stack in stacks_raw:
s_id = str(stack['id'])
export["boards"][str(board["id"])]["stacks"][s_id] = {
"id": stack['id'],
"title": stack['title'],
"boardId": stack['board_id'],
"deletedAt": 0,
"lastModified": 0,
"order": stack.get("ordinal", 0),
"ETag": "",
"cards": []
}
for card in [c for c in cards_raw if c["stack_id"] == stack['id']]:
card_attachments = None
card_labels = []
card_users = []
for att in attachments:
if att["card_id"] == card["id"]:
card_attachments = None # TODO: add detailed attachment mapping if needed
break
for l in assigned_labels:
if l["card_id"] == card["id"]:
card_labels.append(l["label_id"])
for u in assigned_users:
if u["card_id"] == card["id"] and u["type"] == 0:
cursor.execute("SELECT uid, displayname FROM oc_users WHERE uid = %s", (u['participant'],))
user_info = cursor.fetchone() or {"uid": u['participant'], "displayname": u['participant']}
card_users.append({
"id": u["id"],
"participant": {
"primaryKey": u['participant'],
"uid": user_info['uid'],
"displayname": user_info['displayname'],
"type": u['type']
},
"cardId": u['card_id'],
"type": u['type']
})
export["boards"][str(board["id"])]["stacks"][s_id]["cards"].append({
"id": card["id"],
"title": card["title"],
"description": card.get("description", ""),
"stackId": card["stack_id"],
"type": "plain",
"lastModified": 0,
"lastEditor": None,
"createdAt": 0,
"labels": card_labels,
"assignedUsers": card_users,
"attachments": card_attachments,
"attachmentCount": None,
"owner": {
"primaryKey": card['owner'],
"uid": card['owner'],
"displayname": card['owner'],
"type": 0
},
"order": card.get("ordinal", 0),
"archived": bool(card.get("archived", 0)),
"done": None,
"duedate": card.get("duedate"),
"deletedAt": 0,
"commentsUnread": 0,
"commentsCount": 0,
"ETag": "",
"overdue": 0,
"boardId": board["id"],
"board": {
"id": board["id"],
"title": board["title"]
},
"referenceData": None,
"comments": []
})
# Write JSON output
with open(f"deck_board_{BOARD_ID}.json", "w") as f:
json.dump(export, f, indent=2, default=str)
cursor.close()
conn.close()
print(f"Exported board {BOARD_ID} to deck_board_{BOARD_ID}.json")